How to Add Row Numbers to Sorted SELECT Results in MySQL?

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':

<code class="language-sql">SELECT itemID, COUNT(*) as ordercount
FROM orders
GROUP BY itemID
ORDER BY ordercount DESC;</code>

This query returns the item count but not the row number.

Solution:

To retrieve the line number, use the following technique:

1. Initialize a variable to store the current line number:

<code class="language-sql">SET @rank=0;</code>

2. Modify the original query to use variables to assign row numbers:

<code class="language-sql">SELECT @rank:=@rank+1 AS rank, itemID, COUNT(*) as ordercount
FROM orders
GROUP BY itemID
ORDER BY ordercount DESC;</code>

3. Execute another SELECT statement to retrieve the final row count:

<code class="language-sql">SELECT @rank;</code>

Example:

The following query returns the row number and item count:

<code class="language-sql">SET @rank=0;
SELECT @rank:=@rank+1 AS rank, itemID, COUNT(*) as ordercount
  FROM orders
  GROUP BY itemID
  ORDER BY ordercount DESC;
SELECT @rank;</code>

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!