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)

Mosenthal

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 – Ammons

Possible duplicate of ROW_NUMBER() in MySQL – Brody

Answer 1

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.

Byword

That adds the rank to the result set, but doesn't put them in the proper order - updated question with results – Mosenthal

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). – Byword

Can you show an example of this? How would I wrap the selects? – Mosenthal

Check out swamibebop's answer – Gauhati

Beautiful, thanks. Used this for an insert..select pop script to incr assumed relational ids. – Embayment

the answer below from swamibebop is better because it's done in only one query – Pasho

how can I use this through PHP-PDO, please suggest. – Unvoice

@MikeCialowicz, This doesn't work. Refer to my solution or Swamibebop's solution for the right answer. – Nonresident

Answer 2

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;

Achromat

Thank for clarifying, this solved the out-of-order problem I was having. – Gauhati

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. – Maryellen

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. – Burnet

Can anyone explain the relevance of t1 and t2? – Lefler

@Jared, MySQL syntax just needs something to be there. It can be anything, even x and y. – Nonresident

Answer 3

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;

Nonresident

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)? – Ethanethane

@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. – Ethanethane

Would you have any idea why the ORDER BY is not working whenever its not in the field list? See my result: hastebin.com/aluqefunoy.rb – Disbelieve

Answer 4

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; 

Latreshia

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. – Nonresident

@Nonresident 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. – Ethanethane

Answer 5

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

Rna

Answer 6

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;

Rubefaction

Answer 7

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.

Semeiology