MySql - Get row number on select
- by George
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.
