mysql row number is not working properly with order

Question

I have a Store Database with the following tables :

1 - Provider(ProviderID,Name,Country)

2 - Product(ProductID,ProviderID,ProductPrice)

3 - Command(CommandID,ProductID,ProductQuantity,CommandDate)

I made a query that counts the gain by year

SELECT EXTRACT(YEAR FROM COMMAND_DATE) AS year, 
SUM(PRODUCT_PRICE*PRODUCT_QUANTITY) AS gain
FROM PRODUCT JOIN COMMAND ON PRODUCT.PRODUCT_ID=COMMAND.PRODUCT_ID
GROUP BY year
ORDER BY year

This is the output :

Now I want to display the row number just like Oracle, so I used this query :

SET @currentRow = 0;
SELECT @currentRow := @currentRow + 1 AS counter,
EXTRACT(YEAR FROM COMMAND_DATE) AS year,
SUM(PRODUCT_PRICE*PRODUCT_QUANTITY) AS gain
FROM PRODUCT JOIN COMMAND ON PRODUCT.PRODUCT_ID=COMMAND.PRODUCT_ID
GROUP BY year
ORDER BY year

But I don't get what I want

It seems that order is affecting the row number. I want it to start from 1. Ordering by counter is not an option because I need to order by years.

This is how I want it to be :

1 2014 1863
2 2015 889
3 2016 2626
...

score 1 · Accepted Answer · answered Jan 23 '19 at 16:35

With group by, you need a subquery:

SELECT (@currentRow := @currentRow + 1) AS counter, y.*
FROM (SELECT EXTRACT(YEAR FROM COMMAND_DATE) AS year,
             SUM(PRODUCT_PRICE*PRODUCT_QUANTITY) AS gain
      FROM PRODUCT JOIN
           COMMAND
           ON PRODUCT.PRODUCT_ID = COMMAND.PRODUCT_ID
      GROUP BY year
      ORDER BY year
     ) y CROSS JOIN
     (SELECT @currentRow := 0) params;

Or, you can use ROW_NUMBER() OVER (ORDER BY YEAR), if you are using MySQL 8+.

mysql row number is not working properly with order

1 Answers1