set @rank := 0;
select
s.nickname,
@rank := @rank + 1 as rank,
sum(ro.total_sales - ro.taxes) / 100 as sales
from rpt_repair_order ro
inner join shop s
on ro.shop_id = s.id
where DATE(CONVERT_TZ(ro.posted_date, 'UTC', s.time_zone_id)) > '2020-01-19' and DATE(CONVERT_TZ(ro.posted_date, 'UTC', s.time_zone_id)) <= '2020-01-25'
group by s.nickname
order by sales asc;
>> like shown in this example
And in the SQL I already posted...
>> Any idea why this would be happening?
I would need sample data with the results. In the link I provided store AZ005 with 60,000 is ranked 1.
It appears it can be done pre v8 but not as easy.
Something I can create a test case with.
If you are going to do a lot of work, a dbfiddle.uk already set up with tables and data would be perfect. That way we can just focus on the results and not take the time to convert what you post into a test case.
Then provide the expected results from the fiddle.
select
ro.shop_id,
s.nickname,
sum(ro.total_sales - ro.taxes) / 100 as sales,
count(ro.repair_order_id) as car_count,
@curRank := @curRank + 1 as curRank
from rpt_repair_order ro
inner join shop s
on ro.shop_id = s.id
, (SELECT @curRank := 0) r
where DATE(CONVERT_TZ(ro.posted_date, 'UTC', s.time_zone_id)) > '2020-01-19' and DATE(CONVERT_TZ(ro.posted_date, 'UTC', s.time_zone_id)) <= '2020-01-25'
group by ro.shop_id
order by sales desc;
I prefer dbfiddle.uk but thanks for that.
Now I need the expected results from the test data you provided.
Using the SQL in the first fiddle I posted, here is what I have:
https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=1ad9f1bc74b8dc6b4d19a6569942907c
SELECT shopid,
sales,
carcount,
@curRank := @curRank + 1 AS rank
FROM test t, (SELECT @curRank := 0) r
order by sales desc
;
The results I get are:
shopid | sales | carcount | rank |
---|---|---|---|
CO005 | 45000.0000 | 111 | 1 |
AZ005 | 36000.0000 | 118 | 2 |
IN007 | 32000.0000 | 115 | 3 |
TX001 | 20000.0000 | 100 | 4 |
I can only test against what you provide.
Do you have a sales column in those tables? That is could be the problem.
I need a test case that shows the problem. Please provide both tables with sample data that shows the rank not correct using the SQL you provide.
I just need test data to run your query against that shows the problem. If you can show it with just three records, that is all I need. If you need 100, then that is what I need.
I want to copy/paste the query you are running that shows the rank not working. I'll then attempt to correct the SQL so it works as you want.
What version of MySQL?
At least in version8, it has the rank function that should be almost exactly as SQL Server:
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_rank