MySQL: Retrieve row number from sorted SELECT results
Question:
How to get the row number of the sorted item in the SELECT statement of MySQL?
Background:
Consider a table called 'orders' containing columns 'orderID' and 'itemID'. The query shown below calculates the order quantity by 'itemID':
SELECT itemID, COUNT(*) as ordercount FROM orders GROUP BY itemID ORDER BY ordercount DESC;
This query returns the item count but not the row number.
Solution:
To retrieve the line number, use the following technique:
SET @rank=0;
SELECT @rank:=@rank+1 AS rank, itemID, COUNT(*) as ordercount FROM orders GROUP BY itemID ORDER BY ordercount DESC;
SELECT @rank;
Example:
The following query returns the row number and item count:
SET @rank=0; SELECT @rank:=@rank+1 AS rank, itemID, COUNT(*) as ordercount FROM orders GROUP BY itemID ORDER BY ordercount DESC; SELECT @rank;
Output:
<code>1 | 388 | 3 2 | 234 | 2 3 | 693 | 1 4 | 3432 | 1 5 | 3459 | 1</code>
Final row count is 5.
The above is the detailed content of How to Add Row Numbers to Sorted SELECT Results in MySQL?. For more information, please follow other related articles on the PHP Chinese website!