MySQL - Get row number on select
MysqlSqlRow NumberMysql Problem Overview
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 | 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.
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)
Mysql Solutions
Solution 1 - Mysql
Take a look at http://www.fromdual.com/ranking-mysql-results">this</a>;.
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;
SELECT @rank;
The last select is your count.
Solution 2 - Mysql
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;
Solution 3 - Mysql
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:
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;
Solution 4 - Mysql
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;
Solution 5 - Mysql
It's now builtin in MySQL 8.0 and MariaDB 10.2:
SELECT
itemID, COUNT(*) as ordercount,
ROW_NUMBER OVER (PARTITION BY itemID ORDER BY rank DESC) as rank
FROM orders
GROUP BY itemID ORDER BY rank DESC
Solution 6 - Mysql
SELECT RANK() OVER(ORDER BY Employee.ID) rank, forename, surname, Department.Name, Occupation.Name
FROM Employee
JOIN Occupation ON Occupation.ID = Employee.OccupationID
JOIN Department ON Department.ID = Employee.DepartmentID
WHERE DepartmentID = 2;