MySql - Get row number on select

Posted by George on Stack Overflow See other posts from Stack Overflow or by George
Published on 2010-03-26T00:10:00Z Indexed on 2010/03/26 0:13 UTC
Read the original article Hit count: 740

Filed under:
|
|

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.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about mysql-query