I move the blog to my new domain, please visit : http://jimmod.com/blog/2008/09/displaying-row-number-rownum-in-mysql/ for latest update
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
Technorati Tags: mysql, select, query, rownum, row number, rank
Man, tank’s for the information, you just save a lot of work. Tank’s a lot
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!
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
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.
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!
[…] the credit goes to this blog post, but I feel it’s always good to propagate tips like this. MORE […]
you realy help…
If the score is the same score, how can you make the rank equivalent to the same rank number?
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
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
The most sensible method would be to get your scores out of the database and then do the grouping in php.
@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
Moving to http://jimmod.com/blog/?p=78
Please leave your comment at new domain.