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: 812
        
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