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)

Answer 1

Try using this query:

SET @rank=0;

SELECT @rank:=@rank+1 AS rank, itemID, COUNT(*) as ordercount

  FROM orders

  GROUP BY itemID

  ORDER BY ordercount DESC;

SELECT @rank;

Note: The last select is for the count.