Enable an additional counter in the MySQL result set

Question

Can I add an extra counter to the MySQL result set? I have the following query that returns me two columns. I need an extra column (only as a result) indicating the row of each row in the result set.

select orderid, round(sum(unitprice * quantity),2) as value from order_details group by orderid order by 2 desc limit 10 

I need something like the following:

 10865 1 17250.00 11030 2 16321.90 10981 3 15810.00 10372 4 12281.20 10424 5 11493.20 

Answer 1

Try the following:

 SET @counter = 0; Select sub.* FROM ( select orderid, (@counter := @counter +1) as counter, round(sum(unitprice * quantity),2) as value from order_details group by orderid ) sub order by 2 desc 

Answer 2

Try to execute

 SET @counter = 0; select orderid, (@counter:= @counter + 1) as counter, round(sum(unitprice * quantity),2) as value from order_details group by orderid order by 3 desc limit 10 

Hope this helps ...

Answer 3

Based on two answers, I managed to get the following:

 SET @counter = 0; Select sub.orderid,sub.value,(@counter := @counter +1) as counter FROM ( select orderid, round(sum(unitprice * quantity),2) as value from order_details group by orderid ) sub order by 2 desc limit 10 

The original responses showed identifiers from the internal query, resulting in large indexes with large spaces. Using the modification, I get only the range from 1 to x, which I need for my pgfplots LaTeX chart.