With MySQL how can I generate a column containing the record index in a table

Question

Is there any way I can get the actual row number from a query   I want to be able to order a table called league girl by a field called score  and return the username and the actual row position of that username   I m wanting to rank the users so i can tell where a particular user is  ie  Joe is position 100 out of 200  i e   User Score Row Joe  100    1 Bob  50     2 Bill 10     3   I ve seen a few solutions on here but I ve tried most of them and none of them actually return the row number   I have tried this   SELECT position  username  score FROM  SELECT  row     row   1 AS position  username  score         FROM league girl GROUP BY username ORDER BY score DESC     As derived      but it doesn t seem to return the row position   Any ideas

User · Answer

You can also use  SELECT  curRow    ifnull  curRow 0    1 Row        to initialise the counter variable

User · Answer

SELECT  i   i 1 AS iterator  t   FROM tablename t  SELECT  i  0  foo

User · Answer

You may want to try the following   SELECT  l position           l username           l score           curRow     curRow   1 AS row number FROM    league girl l JOIN     SELECT  curRow    0  r    The JOIN  SELECT  curRow    0  part allows the variable initialization without requiring a separate SET command   Test case   CREATE TABLE league girl  position int  username varchar 10   score int   INSERT INTO league girl VALUES  1   a   10   INSERT INTO league girl VALUES  2   b   25   INSERT INTO league girl VALUES  3   c   75   INSERT INTO league girl VALUES  4   d   25   INSERT INTO league girl VALUES  5   e   55   INSERT INTO league girl VALUES  6   f   80   INSERT INTO league girl VALUES  7   g   15     Test query   SELECT  l position           l username           l score           curRow     curRow   1 AS row number FROM    league girl l JOIN     SELECT  curRow    0  r WHERE   l score  gt  50    Result    ---------- ---------- ------- ------------    position   username   score   row number    ---------- ---------- ------- ------------           3   c             75            1            5   e             55            2            6   f             80            3    ---------- ---------- ------- ------------  3 rows in set  0 00 sec

User · Answer

Here comes the structure of template I used     select             this is a row number counter               select  rownum     rownum   1 from   select  rownum    0   d2              as rownumber            d3     from      select d1   from table name d1   d3   And here is my working code   select                   select  rownum     rownum   1 from   select  rownum    0   d2               as rownumber             d3   from     select     year  d1 date    month  d1 date    count  d1 id       from       maindatabase d1     where          d1 date  gt    2013-01-01    and   d1 date  lt    2014-12-31          group by   YEAR  d1 date    MONTH  d1 date     d3

User · Answer

I found the original answer incredibly helpful but I also wanted to grab a certain set of rows based on the row numbers I was inserting  As such  I wrapped the entire original answer in a subquery so that I could reference the row number I was inserting   SELECT   FROM         SELECT     curRow     curRow   1 AS  row number      FROM db tableName   SELECT  curRow    0  r   as temp WHERE temp row number BETWEEN 1 and 10    Having a subquery in a subquery is not very efficient  so it would be worth testing whether you get a better result by having your SQL server handle this query  or fetching the entire table and having the application web server manipulate the rows after the fact   Personally my SQL server isn t overly busy  so having it handle the nested subqueries was preferable

User · Answer

If you just want to know the position of one specific user after order by field score  you can simply select all row from your table where field score is higher than the current user score  And use row number returned   1 to know which position of this current user   Assuming that your table is league girl and your primary field is id  you can use this   SELECT count id    1 as rank from league girl where score  gt   lt your user score gt

User · Answer

I know the OP is asking for a mysql answer but since I found the other answers not working for me     Most of them fail with order by  Or they are simply very inefficient and make your query very slow for a fat table   So to save time for others like me  just index the row after retrieving them from database  example in PHP    users   UserRepository  loadAllUsersAndSortByScore     foreach  users as  index  gt  amp  user        user  rank      index 1      example in PHP using offset and limit for paging    limit   20    page size  offset   3    page number   users   UserRepository  loadAllUsersAndSortByScore     foreach  users as  index  gt  amp  user        user  rank      index 1   limit   offset-1

User · Answer

Assuming MySQL supports it  you can easily do this with a standard SQL subquery   select       count    from league girl l1 where l2 score  gt  l1 score and l1 id  lt  gt  l2 id  as position      username      score from league girl l2 order by score    For large amounts of displayed results  this will be a bit slow and you will want to switch to a self join instead

[mysql] With MySQL, how can I generate a column containing the record index in a table?

Examples related to mysql

Examples related to indexing