Displaying row number (rownum) in MySQL

Sometimes you need to do query with MySQL for reporting.
And you’ll need to display the row number/ranking in query result.

Example you have this table:

table : player
fields : playerid, name & score

For reporting purpose you need to query the top 10 (highest score).
The result should be : rank, memberid, name, score.
Example :
rank playerid name score
———————————–
1 A1029 Jimmy 100
2 A9830 Lia 98
3 B28d0 Lulu 90


10 B8789 Lele 50

Now you can easily query the top 10 by using ‘limit’ and ‘order by’, but how to automatically add row number in query result?

Here’s how you do it:

select @rownum:=@rownum+1 ‘rank’, p.* from player p, (SELECT @rownum:=0) r order by score desc limit 10;

Try it :)

This will create a variable rownum, initialize with value 0 & increase it by 1 for every record

————————————————————————————————————————————

One of the comment asking different rule for ranking (Thank to @justin below that raising this issue).
He want players that have same score should have same ranking.
So the result should be like:

rank playerid name score
———————————–
1 A1029 Jimmy 100
2 A9830 Lili 90
2 B28d0 Lulu 90
3 X9830 Lolo 70
3 Y28d0 Lele 70

After testing some query I find a way to do this by having a variable that save the score then compared on next record.
If the score is the same the rank will not changed, if it’s not the same (that mean the score is less then previous – because it’s ordered desc) the rank will be increased by 1.

Here’s the query:

select IF(@score=p.score, @rownum, @rownum:=@rownum+1) rank2, p.*, (@score:=p.score) dummy from player p, (SELECT @rownum:=0) x, (SELECT @score:=0) y order by score desc limit 10;

————————————————————————————————————————————
Technorati Tags: , , , , ,

72 comments

  1. Thanks man, This is exactly what i was looking for. I’ve seen this same basic idea, but they used a SET @rownum; before the SELECT statement. The implementation i have needed something that was one single SELECT statement and this was just what i needed. Thanks again!

  2. Hello,

    First of all thanks for sharing this with us!

    I have been searching and trying and trying to find a solution to this problem

    I am designing a gallery which lets you navigate through pages of photos.
    I want to select a number of rows between a specific range to display just 12 photos at a time, but so that users can go onto the next page, and it show the next 12 entries.

    I’ve tried this with your script but it won’t let me enter “WHERE rank BETWEEN 1 and 12″ anywhere inside it. It keeps telling me that rank isn’t a column.

    Any push in the right direction would be much appreciated.

    Thanks
    Tom

  3. Hi Tom,

    You can try this query:

    select * from select @rownum:=@rownum+1 ‘rank’, p.* from player p, (SELECT @rownum:=0) r order by score desc limit 10) rank_player where rank between 2 and 4;

    With that you can add condition for rank.
    The example above will only display rank 2 until 4.

  4. Jim; Thanks for this! You missed a parenthesis however “select * from (select” is what it should say.

    Brilliant query, however I’m having a problem where my server re-orders the result set only after the counter has been incremented, so the ranks seem jumbled up.

    When I use your subquery, my original “ORDER BY score DESC” just gets invalidated by the “user_rank ORDER BY rank”.

    What fixed it for me, was this;
    SELECT *, @rownum:=@rownum+1 as rank FROM (SELECT * FROM player p ORDER BY score DESC LIMIT 10) user_rank, (SELECT @rownum:=0) r

    Thanks again!

  5. If you would like to select the row number for an individual record (based on an id).. with ordering and other conditions…

    (select @row_number:=0); select row_number from (select id,@row_number:=@row_number+1 as row_number from some_table order by some_column asc) sub1 where id = 28

  6. skn3
    Here is a example table

    DROP TABLE IF EXISTS `player2`;
    CREATE TABLE `player2` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(45) NOT NULL,
    `score` int(11) NOT NULL DEFAULT ’0′,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


    – Dumping data for table `player2`

    LOCK TABLES `player2` WRITE;
    /*!40000 ALTER TABLE `player2` DISABLE KEYS */;
    INSERT INTO `player2` VALUES (1,’bill’,200),(2,’john’,150),(3,’tom’,50),(4,’jack’,50),(5,’cal’,200);
    /*!40000 ALTER TABLE `player2` ENABLE KEYS */;
    UNLOCK TABLES;

    SELECT * FROM player2 p;
    id name score
    1, ‘bill’, 200
    2, ‘john’, 150
    3, ‘tom’, 50
    4, ‘jack’, 50
    5, ‘cal’, 200

    Ranking players
    SELECT @rownum:=@rownum+1 ‘rank’,p. `name`, p.`score` FROM player2 p,
    (SELECT @rownum:=0) r order by score desc ;

    When I run a rank of score I get
    rank name score
    1 ‘bill’, 200
    2 ‘cal’, 200
    3 ‘john’, 150
    4 ‘tom’, 50
    5 ‘jack’, 50

    I would like to get the result below
    rank name score
    1 ‘bill’, 200
    1 ‘cal’, 200
    2 ‘john’, 150
    4 ‘tom’, 50
    4 ‘jack’, 50

    Thank you
    Tom

  7. @Tom:

    Maybe this not exactly what you need, but this concept will help.

    SQL : SELECT @rownum:=@rownum+1 “rank”, IF(@scorep.score, @rownum2:=@rownum, @rownum2:=@rownum2) ‘rank2′, p.name, @score:=p.score ‘score’ FROM player2 p, (SELECT @rownum:=0, @score:=0, @rownum2:=1) r order by score desc

    Result :
    rank rank2 name score Ascending
    1 1 bill 200
    2 1 cal 200
    3 3 john 150
    4 4 tom 50
    5 4 jack 50

  8. This is the result I get when I run Query.
    In rank2 I get all one’s
    Result:
    rank,rank2,name,score
    1, 1, ‘bill’, 200
    2, 1, ‘cal’, 200
    3, 1, ‘john’, 150
    4, 1, ‘tom’, 50
    5, 1, ‘jack’, 50
    Query below
    SELECT @rownum:=@rownum+1 rank,
    IF(@scorep.score, @rownum2:=@rownum, @rownum2:=@rownum2) ‘rank2,
    p.name, @score:=p.score ’score’ FROM player2 p,
    (SELECT @rownum:=0, @score:=0, @rownum2:=1) r
    order by score desc;

    What did I do wrong?
    jimlife
    Your result is what I am looking for.
    Thank you
    Tom

  9. Sorry:::about the Help with comment but I kept getting the message below.
    Hmmm, your comment seems a bit spammy. We’re not real big on spam around here.

    Please go back and try again.

    Sorry about that post it seem to be working now.
    Tom

  10. skn3
    I am not that good at PHP or MySQL.
    I am an old retired man I do this for a hobby and trying to learn a about PHP, MySQL, ASP.NET Ajax and Visual Web Developer. I seem to learn better by example more than any thing else.

    Thank you
    Tom

  11. @Tom :

    Please try this query

    SELECT
    @rank:=@rank+1 rank,
    IF(@score=p.score, @rank2:=@rank2, @rank2:=@rank) rank2,
    p.name, @score:=p.score score
    FROM
    player2 p,
    (SELECT @rank:=0, @score:=0, @rank2:=1) r
    order by score desc;

    The concept is we create 3 variable.
    @rank = normal rank.
    @rank2 = only set equal to @rank if the previous score is different.
    @score = to hold previous score value.
    The trick is at IF() function

  12. jimmy

    That Query works.
    result from query
    rank1,rank2,name,score
    1, 1, ‘alex’, 700
    2, 2, ‘bill’, 200
    3, 2, ‘tom’, 200
    4, 2, ‘cal’, 200
    5, 5, ‘john’, 150
    6, 6, ‘jack’, 100
    7, 7, ‘rom’, 50
    8, 7, ‘hank’, 50
    9, 7, ‘bob’, 50

    Thanks
    Tom

  13. I’m trying to implement this solution to show rank/rownum, however, I’m using a group by query and
    the row numbers returned are as follows:

    select @rownum:=@rownum+1 ‘rank’, T.team_disp_name ‘Team Name’, SUM(Off.at_bats) ‘Team At Bats’
    FROM Teams T, Game_Off_Stats Off, (SELECT @rownum:=0) r
    WHERE T.team_id = Off.team_id
    Group BY T.team_id
    Order By SUM(Off.at_bats) Desc limit 10;

    Returns:

    ‘rank’ Team Name Team At Bats
    —— ———— ————
    14 Texas 5728
    19 Seattle 5643
    27 Detroit 5641
    25 Minnesota 5641
    21 St. Louis 5636
    2 Pittsburgh 5628
    12 Kansas City 5608
    6 NY Mets 5606
    1 Atlanta 5604
    3 Boston 5596

    As you can see, the Rank is showing the team’s original row number and not reflecting it’s rank.

    Desired Results:

    ‘rank’ Team Name Team At Bats
    —— ———— ————
    1 Texas 5728
    2 Seattle 5643
    3 Detroit 5641
    4 Minnesota 5641
    5 St. Louis 5636
    6 Pittsburgh 5628
    7 Kansas City 5608
    8 NY Mets 5606
    9 Atlanta 5604
    10 Boston 5596

    How can I solve this rownum problem and list them 1-10 order as shown above?

    Any help would be greatly appreciated.

    Thanks in advance.

    ~Jim Zak~
    Truly Enjoy Helping People Help Themselves

  14. @Jim Zak

    I have a problem like you. I haven’t found the solution with single query. So I use “SET” for the query, like this :

    SET @rownum :=0;
    SELECT @rownum:=@rownum+1 ‘rank’, T.team_disp_name ‘Team Name’, SUM(Off.at_bats) ‘Team At Bats’
    FROM Teams T, Game_Off_Stats Off
    WHERE T.team_id = Off.team_id
    Group BY T.team_id
    Order By SUM(Off.at_bats) Desc limit 10;

  15. i want to ask some question i have a code
    $sql = ‘SET @numrow :=0; SELECT DISTINCT t.poin_kelas,t.bonus,t.nama_owner, t.total, t.rank FROM table_tr_outlet a INNER JOIN (SELECT @numrow := @numrow +1 AS rank, table_owner.nama_owner, SUM( table_tr_outlet.class_point ) as poin_kelas , table_tr_outlet.kode_nk, table_nk.total_book AS total, table_nk.bonus FROM ((table_tr_outlet LEFT JOIN table_outlet ON table_outlet.outlet_id = table_tr_outlet.outlet_id)LEFT JOIN table_nk ON table_nk.kode_nk = table_tr_outlet.kode_nk)LEFT JOIN table_owner ON table_owner.owner_id = table_outlet.owner_id WHERE table_tr_outlet.program_id =’222′ GROUP BY table_tr_outlet.kode_nk ORDER BY total DESC )t ON a.kode_nk = t.kode_nk WHERE t.nama_owner LIKE ‘%RONNY%”;
    it can run in php my admin but i when i apply it
    $sql = ‘SET @numrow :=0; SELECT DISTINCT t.poin_kelas,t.bonus,t.nama_owner, t.total, t.rank FROM table_tr_outlet a INNER JOIN (SELECT @numrow := @numrow +1 AS rank, table_owner.nama_owner, SUM( table_tr_outlet.class_point ) as poin_kelas , table_tr_outlet.kode_nk, table_nk.total_book AS total, table_nk.bonus FROM ((table_tr_outlet LEFT JOIN table_outlet ON table_outlet.outlet_id = table_tr_outlet.outlet_id)LEFT JOIN table_nk ON table_nk.kode_nk = table_tr_outlet.kode_nk)LEFT JOIN table_owner ON table_owner.owner_id = table_outlet.owner_id WHERE table_tr_outlet.program_id =’$id’ GROUP BY table_tr_outlet.kode_nk ORDER BY total DESC )t ON a.kode_nk = t.kode_nk WHERE t.nama_owner LIKE ‘%$search%”;
    $hasil= mysql_query($sql);
    while($rows = mysql_fetch_array($hasil))
    {
    $rank=$rows["rank"];
    $class_point=$rows["poin_kelas"];
    $total=$rows["total"];
    $bonus=$rows["bonus"];
    $nama_owner=$rows["nama_owner"];

    ?>

    it can’t display and there is an error message

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:xampphtdocsmarketingtable_programlihat_pemenang_search.php on line 26

    anyone can help my problem?if i delete SET @numrow :=0; it can work but it can’t show the rank..thanks before

  16. Sorry for not replying soon. Pretty busy lately.

    @JimZak:
    How about if we put the rownum outside:
    select @rownum:=@rownum+1 ‘rank’, A.* from
    (
    select T.team_disp_name ‘Team Name’, SUM(Off.at_bats) ‘Team At Bats’
    FROM Teams T, Game_Off_Stats Off
    WHERE T.team_id = Off.team_id
    Group BY T.team_id
    Order By SUM(Off.at_bats) Desc limit 10
    ) A, (SELECT @rownum:=0) r;

  17. @nay:
    I don’t think you can put 2 sql queries in PHP like that.
    Try to find a single query or run it separate mysql_query (not sure about this though)

  18. Thanks for this small snippet. It helped me a lot. I don’t know if someone can use my piece of code with an integrated JOIN:

    select @row_number:=0);
    SELECT row_number FROM (SELECT u.id, c2u.community_id, @row_number:=@row_number+1 AS row_number FROM communities2user AS c2u JOIN user AS u ON c2u.user_id = u.id WHERE c2u.community_id = 1 ORDER BY u.points DESC ) sub1 WHERE id = 1

  19. Hello Jimmy,

    I’m converting a legacy Cobol application with flat files to a MySQL environment. One of the problems I encountered that new records were assigned a sequential number as key, but due to its legacy I couldn’t just use an auto-incremented value. Your solution provided an easy way to just increment the previous highest value for the complete set of new records, thus leaving at least for a little longer some dark hairs amidst the grays :)

    Thanks a lot.

  20. Hi,I find out that your web log is very beneficial and useful and we were interested if there is a possibility of obtaining More article content like this on your site. If you willing to assist us out, we would be willing to compensate you… Best wishes, Pete Kitterman

  21. I’m running the following query from client desktop via mysql connector
    SELECT @rownum:=@rownum + 1 ‘GroupNum’ ,
    users.recId ,
    users.firstName,
    users.Surname
    FROM users, (select @rownum := 0)r

    and receiving the error “Parameter @rownum must be defined”. So I’m assuming it’s treating rownum as external parameter.
    How do i fix this please

  22. @czeshirecat:
    Strange, your query should be correct. I try to make similar query and it’s run ok.
    Do you test it from MySQL client or from programming language?
    And which MySQL version do you used?

  23. I ran it from a mysql .net connector in visual studio c# .net project. It DID run ok in mysql query browser tho.

  24. @czeshirecat:
    That’s mean it have issue on the .net connector.
    Not sure what suggestion I can give you because I’m not experience in .Net

    How if you try create a stored procedure with that select query and call it from .Net

  25. Please need help,

    I’m beginner about mysql. And I have problem,
    when I using ur query I have result
    Rank | name | score | no
    1. | novey| 98. | 2
    2. | nang | 87. | 3
    3. | fika | 67. | 1

    The result above, I have desc using column score, but I wanna desc using column no, and the result is :
    Rank | name | score | no
    3 | fika | 67 | 1
    1 | novey| 98 | 2
    2 | nang | 87 | 3
    How I can do this,its posible using view and reorder theme? thanks jimmy.

  26. How can you use this type of query to select by row #?

    ie. I have a table with multiple records – but the ID’s are not all sequential.

    I need to select a random row – so I’d like to SELECT COUNT(*) to get the total number of rows, rand(1,$count) to get a random row # then use a select to get at that specific row.

  27. Thank you for sharing this great technique.

    I needed to set position values for products in a category by alphabetical order – this allowed me to reduce what would have been a couple hundred update queries into one. Not sure if this is the best way to do it, here’s the code in case someone needs to do something similar.

    UPDATE products SET `position` = (SELECT @rownum:=@rownum+1 FROM (SELECT @rownum:=0) AS temp) WHERE `category_id` = 5 ORDER BY `name` ASC

  28. This does work with INNER JOIN’s, if you do “SELECT @rownum:=@rownum+1 ‘rank’,a
    FROM b
    INNER JOIN c ON (b.a=c.d)
    INNER JOIN (SELECT @rownum:=0) AS r”

  29. Hi,
    It is possible to take rank by rownum,but If two or more scores(marks) are equal, we need to give same rank.
    Is it possible?

    Regards,
    Justin

  30. @Justin:
    It possible with this query (still a bit mess):

    select @rownum:=@rownum+1 rank, IF(@score=p.score, @rownum2, @rownum2:=@rownum2+1) rank2, p.*, (@score:=p.score) dummy from player p, (SELECT @rownum:=0) x, (SELECT @rownum2:=0) c, (SELECT @score:=0) r order by score desc limit 10

    rank2 will display the ranking you want (no increment on same score).

    You should also need to check stored procedure solution which may more appropriate for this case.

  31. Thanks!
    Needed it to append commas to all rows except the last for something I was working on:

    SELECT CONCAT(character,
    IF(@rownum=rowqty.limit,”,’,’)
    )
    FROM sandbox,
    (SELECT @rownum:=0) r,
    (SELECT COUNT(*) AS ‘limit’
    FROM sandbox) as rowqty
    WHERE @rownum:=@rownum+1;

  32. Thank you men ! It works very good now ! I was looking for this problem since 2 hours but without you… i would need 1 day maybe ! Thank you !

  33. Hi everybody. I got a problem here. I want to output the number of ranking… but the code below here which top5 will take the same number from contest id. Hope you can help me.

    SELECT
    @rownum:=@rownum+1 top5
    ,g.id
    ,g.team
    ,v.image
    ,v.vote
    FROM
    contest as g
    ,(SELECT @rownum:=0) r
    LEFT JOIN
    vote as v
    ON
    g.id = v.contest_id
    WHERE
    g.ranking_id = {$ranking_id}
    AND v.variation_no = 1
    AND g.flg = 1
    ORDER BY
    v.vote DESC
    LIMIT
    5

  34. @Airforce:

    I only notice the sql is wrong inn the FROM, the (SELECT @rownum:=0) r shouldn’t be joined. It should be :
    FROM
    (SELECT @rownum:=0) r,
    contest as g
    LEFT JOIN
    vote as v
    ON
    g.id = v.contest_id

  35. Hello,

    I’m trying to use this for a crystal command. But getting an error saying incorrect syntax for version. This is my attempt….can you help??

    Select @rownum:=@rownum+1 ‘ranknum’, cust_name, count(distinct callref) as rank from opencall,(SELECT @rownum:=0) where logdate like ‘%/08/2011%’
    and companyname=”McDonald’s Nederland” group by cust_name order by rank desc

    Thanks in advance
    Annette

  36. @Annete: do you mean crystal report?
    I’m not really familiar with crystal report, if you can explain the error message in more detail maybe I can give you a hint

  37. @Jimmy – I was excited to find this solution while trying to convert a MSSQL query using row_number() to MYSQL. Your solution did not work at first because my query uses INNER JOINS, but your response to Jim Zak on Mar 2,’10 supplied the answer. I have no idea why it works, but it does. Guess it’s time to do some research.

    THANKS – You have been a BIG help!

    Ed

  38. Smart solution!! Thanks Jim.

    When I delete a row from a table then auto increment just missed the identity numbers. but this example save lots of time.

    Your reply with the Tom’s message also very helpful.

    go ahead..

  39. I want to display a table of various datas including a column of s.no. which should be continuous even if removed any data from the table.
    Can you please tell me or this code will work for that?

  40. @CV: I am not sure what you meant by stored procedure, but if you were talking about inner queries (sub-queries), then it does work, as it is my case.
    I have 2 sub-queries and both are using order by. I have just put a 3rd query on top of them with @rownum and it worked. Cheers.

  41. Why can I not select a single record with:

    (SELECT @row_number:=0); SELECT row_number FROM (SELECT userid,@row_number:=@row_number+1 AS row_number FROM users ORDER BY `characters` asc) sub1 WHERE userid = 1

    What am I doing wrong?

  42. Hi,
    Thanks for this piece of code. it works just fine.
    However, my other requirement is to rank within a group.
    For example, I have data as follows

    Model Color Sales
    Mazda Black 15
    Mazda Red 25
    Mazda White 35
    Honda Black 22
    Honda Red 18
    Honda White 43

    How Can I rank within the Model? So for Mazda i need to show 1,2and 3 and same for Honda as well

  43. @Mike:
    I’m not sure why you put the first query ‘(SELECT @row_number:=0);’
    The second query almost correct but you need to give alias to @rownum.
    Here’s the query from my sample if you only want to get 1 record :

    SELECT rank FROM (SELECT @rownum:=@rownum+1 rank, p.* FROM player p, (SELECT @rownum:=0) r ORDER BY score DESC LIMIT 10) a WHERE a.playerid=’A9830′;

  44. @Srinivasan:
    The solution can be done by order the records by model before the sales. Then using IF statement to reset the rank back to 1 if it found the model changed. Query:

    SELECT IF(@model=p.model, @rownum:=@rownum+1, @rownum:=1) rank2, p.*, (@model:=p.model) dummy FROM car p, (SELECT @rownum:=0) r ORDER BY p.model ASC, p.sales DESC LIMIT 10;

    *Table name is ‘car’ in above query

  45. Smart work. But it gives Fatal error when using MySQLCommand to execute this in .Net.
    Any idea?

  46. @Anurag Srivastava:
    Not sure, I only have little .Net experience
    Try which command that making it error, is it the ‘IF’ syntax or the ‘@rownum’ variable?

  47. Hello,
    I have 2 tables, user and profile(with user_id as PK)
    profile stores data like rating, scores etc…
    user table have flags like is_activated, is_deleted…

    {code:sql}
    SELECT @rownum:=@rownum+1 AS rank, t.user_id, t.rating
    FROM profile t
    INNER JOIN user u ON u.id=t.user_id, (SELECT @rownum:=0) r
    WHERE u.is_activated=1 AND u.is_deleted=0
    ORDER BY t.rating DESC;
    {code}

    result is not good, because ‘rank’ is not ordered by rating. mysql somehow use joined table for ordering
    is it regular behavior or just exception? Any workaround?

    Thanks in advance,
    Best, Ivan

  48. @ivan:
    Try to combine the inner join as a temp table, than use the rownum.
    *Note that creating temp table can need processing time on big table.

    SELECT @rownum:=@rownum+1 AS rank, temp.*
    FROM
    (SELECT t.user_id, t.rating
    FROM profile t
    INNER JOIN user u ON u.id=t.user_id
    WHERE u.is_activated=1 AND u.is_deleted=0
    ORDER BY t.rating DESC) temp, (SELECT @rownum:=0) r;

  49. that was EXACTLY what i needed, then i wrapped it between () to make it a subquery and to query a specific player, the position column contained his rank, many thanks!!

  50. try this

    select @rwnum:=@rwnum+1 ‘rk’, pp.* from (select * from (select @rownum:=@rownum+1 ‘rank’, p.* from player p, (SELECT @rownum:=0) r order by score desc) sub1 where name = ‘bill’) pp, (SELECT @rwnum:=0) rr;

  51. Hi Jimmy,
    If you have rank like this:

    1 A1029 Jimmy 100
    2 A9830 Lili 90
    2 B28d0 Lulu 90
    3 X9830 Lolo 70
    3 Y28d0 Lele 70

    and you use this query you posted:

    SELECT rank FROM (SELECT @rownum:=@rownum+1 rank, p.* FROM player p, (SELECT @rownum:=0) r ORDER BY score DESC LIMIT 10) a WHERE a.playerid=’B28d0?

    But the rank for this user displays 3, not 2.
    (This query is fine if none of the other users have the same score)

    How can I display correct rank for the individual user in scenario where some users have the same score.

    Thanks

  52. I was suggested this website by my cousin. I am
    not sure whether this post is written by him as nobody
    else know such detailed about my difficulty. You’re amazing! Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

WP-SpamFree by Pole Position Marketing