MySQL - Get row number on select

Question

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)

For future reference: If you want to order from rank 1 to rank 5, use `ORDER BY rank ASC` (ordering by rank in ASCending order). I guess that is what you mean by *but not properly ordered* — BlueCacti, Jan 09 '15 at 12:57
Possible duplicate of [ROW\_NUMBER() in MySQL](http://stackoverflow.com/questions/1895110/row-number-in-mysql) — Ciro Santilli OurBigBook.com, May 25 '16 at 14:13

score 199 · Accepted Answer · edited Mar 21 '19 at 01:46

199

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.

edited Mar 21 '19 at 01:46

Kareem

5,068
44
38

answered Mar 26 '10 at 00:15

Mike Cialowicz

9,892
9
47
76

1

That adds the rank to the result set, but doesn't put them in the proper order - updated question with results – George Mar 26 '10 at 00:22
1

Try keeping the `ORDER BY ordercount DESC`, and then wrap the whole query in another `SELECT` which gets everything from the first one, but orders by the rank column (0 in this case). – Mike Cialowicz Mar 26 '10 at 00:27
1

Can you show an example of this? How would I wrap the selects? – George Mar 27 '10 at 18:21
9

Check out swamibebop's answer – thaddeusmt Mar 29 '11 at 01:22
Beautiful, thanks. Used this for an insert..select pop script to incr assumed relational ids. – Steven Francolla Dec 13 '11 at 23:16
the answer below from swamibebop is better because it's done in only one query – Nico Mar 20 '14 at 10:56
how can I use this through PHP-PDO, please suggest. – Sumit Kumar Dec 19 '14 at 06:51
1

@MikeCialowicz, This [**doesn't work**](http://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select/29846430#comment47818852_2520392). Refer to my solution or Swamibebop's solution for the right answer. – Pacerier Apr 24 '15 at 11:45

score 187 · Answer 2 · edited Mar 28 '11 at 22:03

187

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;

edited Mar 28 '11 at 22:03

thaddeusmt

15,410
9
67
67

answered Dec 17 '10 at 20:10

swamibebop

1,879
1
11
2

1

Thank for clarifying, this solved the out-of-order problem I was having. – thaddeusmt Mar 29 '11 at 18:49
1

Thanks, this was really useful for me :) I'm surprised there isn't a more straightforward way of getting row 'indexes' from a result set ... but anyway thanks this was handy. – rat Jan 05 '12 at 17:22
You can add a fourth row with an incremental totalcount by changing the first select statement in SELECT \@rn:=\@rn+1 AS rank, itemID, ordercount, \@tot:=\@tot+ordercount as totalcount. To define the initial value of \@tot this should be added after the t2: (SELECT \@tot:=0) t3. Delete the \ before every \@, which I had to use to circumvent mini-Markdown formatting. – Jan Ehrhardt Apr 26 '14 at 00:22
2

Can anyone explain the relevance of `t1` and `t2`? – Jared Apr 30 '14 at 03:11
2

@Jared, MySQL syntax just needs something to be there. It can be anything, even `x` and `y`. – Pacerier Apr 24 '15 at 11:10

score 38 · Answer 3 · edited Jun 24 '18 at 12:37

38

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;

edited Jun 24 '18 at 12:37

Rishikesh Chandra

661
9
25

answered Apr 24 '15 at 11:33

Pacerier

86,231
106
366
634

Do you by chance know why using `@r := @r + 1` in a select statement works, but if it's in a stored procedure with `declare r int; set r = 0;`, it complains (on `r := r +1`)? – Dan M. Dec 17 '16 at 17:03
@Pacerier, also is the order of rows the second select guaranteed somewhere? I know that the order of rows returned by the select without order by clause is not guaranteed anywhere, and the outermost select is exactly that, though it select from the inner ordered select, so it might be an exception. If it's not, however, I cannot see how this is a correct solutions since it'll have the same flaw as Chibu's Mike's - no guarantee in which order select will go through records and number them. – Dan M. Dec 17 '16 at 17:17
Would you have any idea why the ORDER BY is not working whenever its not in the field list? See my result: https://hastebin.com/aluqefunoy.rb – Winter Oct 24 '18 at 20:32

score 13 · Answer 4 · edited Jun 24 '18 at 13:00

13

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;

edited Jun 24 '18 at 13:00

Mohammed Noureldin

14,913
17
70
99

answered Mar 26 '10 at 00:19

Chibu

1,355
7
12

2

Careful, this wouldn't work because `order by` happens **after** the variable `@x` has been evaluated. Try experimenting by ordering using the other columns. Also experiment with both `desc` and `asc`. You'll see that many times they'll fail and the only times when it works, it's by **pure luck** due to the order of your original "select" having the same order as the order of `order by`. See my solution and/or Swamibebop's solution. – Pacerier Apr 24 '15 at 11:43
@Pacerier are you sure about that? I've tired similar query in a different example (basically select from the column of numbers, and number them according to their order) at it seemed that if I ordered by var/row num, when it changed the order of the resulting rows, but each number had the same row num. But if I order by the number column, then the `ASC`/`DESC` would change the order in which those numbers were numbered (from smallest to biggest or vice versa). So it looks like in that case `order by` was evaluated first. – Dan M. Dec 17 '16 at 17:42

score 11 · Answer 5 · answered Dec 02 '19 at 08:22

11

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

answered Dec 02 '19 at 08:22

caram

1,494
13
21

score 1 · Answer 6 · answered Feb 17 '22 at 16:01

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;

MySQL - Get row number on select

6 Answers6

Linked

Related