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 gt  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 gt  SET  rank 0  Query OK  0 rows affected  0 00 sec   mysql gt  SELECT  rank   rank 1 AS rank  itemID  COUNT    as ordercount     - gt  FROM orders     - gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] MySQL - Get row number on select

Examples related to mysql

Examples related to sql

Examples related to row-number