CODEWITHSUNDEEP
mysqlsqlrow-number
George
George

Reputation: 3321

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 | 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)

Upvotes: 205

Views: 445771

Answers (7)

Raheel Shahzad
Raheel Shahzad

Reputation: 156

If someone wants to try a cleaner approach with a single query then this can be a solution.

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

In this case, you don't need specify the initial value which will be null by default and this IFNULL check will assign value of 0 with this query.

Upvotes: 1

Clarius
Clarius

Reputation: 1419

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;

Upvotes: 2

caram
caram

Reputation: 1719

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

Upvotes: 11

Mike Cialowicz
Mike Cialowicz

Reputation: 10030

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;
SELECT @rank;

The last select is your count.

Upvotes: 201

Chibu
Chibu

Reputation: 1353

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;

Upvotes: 13

Pacerier
Pacerier

Reputation: 89773

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;

Upvotes: 39

swamibebop
swamibebop

Reputation: 1899

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;

Upvotes: 189

Related Questions