MySQL - Get row number on select

Can I run a select statement and get the row number if the items are sorted?

I have a table like this:

mysql> describe orders;
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| orderID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| itemID | bigint(20) unsigned | NO | | NULL | |
+-------------+---------------------+------+-----+---------+----------------+


I can then run this query to get the number of orders by ID:

SELECT itemID, COUNT(*) as ordercount
FROM orders
GROUP BY itemID ORDER BY ordercount DESC;


This gives me a count of each itemID in the table like this:

itemID

+--------+------------+
| itemID | ordercount |
+--------+------------+
| 388 | 3 |
| 234 | 2 |
| 3432 | 1 |
| 693 | 1 |
| 3459 | 1 |
+--------+------------+


I want to get the row number as well, so I could tell that itemID=388 is the first row, 234 is second, etc (essentially the ranking of the orders, not just a raw count). I know I can do this in Java when I get the result set back, but I was wondering if there was a way to handle it purely in SQL.

itemID=388

234

Update

Setting the rank adds it to the result set, but not properly ordered:

mysql> SET @rank=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @rank:=@rank+1 AS rank, itemID, COUNT(*) as ordercount
-> FROM orders
-> GROUP BY itemID ORDER BY rank DESC;
+------+--------+------------+
| rank | itemID | ordercount |
+------+--------+------------+
| 5 | 3459 | 1 |
| 4 | 234 | 2 |
| 3 | 693 | 1 |
| 2 | 3432 | 1 |
| 1 | 388 | 3 |
+------+--------+------------+
5 rows in set (0.00 sec)


ORDER BY rank ASC

4 Answers
4

Take a look at this.

Change your query to:

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, itemID, COUNT(*) as ordercount
FROM orders
GROUP BY itemID
ORDER BY ordercount DESC;


ORDER BY ordercount DESC

SELECT

SELECT @rn:=@rn+1 AS rank, itemID, ordercount
FROM (
SELECT itemID, COUNT(*) AS ordercount
FROM orders
GROUP BY itemID
ORDER BY ordercount DESC
) t1, (SELECT @rn:=0) t2;


t1

t2

x

y

Swamibebop's solution works, but by taking advantage of table.* syntax, we can avoid repeating the column names of the inner select and get a simpler/shorter result:

table.*

select

SELECT @r := @r+1 ,
z.*
FROM(/* your original select statement goes in here */)z,
(SELECT @r:=0)y;


So that will give you:

SELECT @r := @r+1 ,
z.*
FROM(
SELECT itemID,
count(*) AS ordercount
FROM orders
GROUP BY itemID
ORDER BY ordercount DESC
)z,
(SELECT @r:=0)y;


@r := @r + 1

declare r int; set r = 0;

r := r +1

You can use MySQL variables to do it. Something like this should work (though, it consists of two queries).

SELECT 0 INTO @x;

SELECT itemID,
COUNT(*) AS ordercount,
(@x:=@x+1) AS rownumber
FROM orders
GROUP BY itemID
ORDER BY ordercount DESC;


order by

@x

desc

asc

order by

ASC

DESC

order by

Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).


Would you like to answer one of these unanswered questions instead?