Jimmy's Blog

Displaying row number (rownum) in MySQL

by jimmy on Sep.09, 2008, under My Life

[Switch to Mobile Edition]

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

Related posts:

  1. Passed SCJP with 94%


70 Comments for this entry

  • CV

    Thank you, it saved me a lot of time. But you can mention that when you use stored procedure with order by, it doesn´t work properly.

  • Naim

    @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.

  • Mike

    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?

  • Srinivasan KP

    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

  • jimmy

    @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′;

  • jimmy

    @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

  • Anurag Srivastava

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

  • jimmy

    @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?

  • ivan

    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

  • jimmy

    @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;

  • Nick

    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!!

  • Anthony

    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;

  • aescart1

    Brilliant, it saves me a lot of time

  • Peter

    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

  • rajesh

    Great Solution…….

  • rajesh

    Good solution given by Jimmy…Thanks..

  • Iwenk

    Gan, saya mau tanya,,
    Biasanya Jumlah Row Num ngikut jumlah baris record. Nah gimana misalkan kalo saya mau masukin Row Num yang jumlahnya lebih dari baris record yang ada di database.. Ilustrasinya seperti gambar ini :

    http://s24.postimg.org/o45f8uq85/Tabel.png

    Mohon pencerahannya…
    Terimakasih sebelumnya…

  • Sadie

    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!

  • adaptor notebook

    Terima kasih untuk berbagi informasi yang berharga ini.

2 Trackbacks / Pingbacks for this entry

Leave a Reply

Spam Protection by WP-SpamFree

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

Visit our friends!

A few highly recommended friends...