Displaying row number (rownum) in MySQL

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

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

JEsus
November 2nd, 2008 on 1:49 am
Man, tank’s for the information, you just save a lot of work. Tank’s a lot
Brad
November 24th, 2008 on 11:12 pm
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!
Tom
November 27th, 2008 on 8:51 pm
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
jimlife
November 28th, 2008 on 11:38 am
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.
Steve
December 15th, 2008 on 7:09 am
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!
arif eko
May 14th, 2009 on 1:55 am
you realy help…
Tom
May 29th, 2009 on 6:58 am
If the score is the same score, how can you make the rank equivalent to the same rank number?
skn3
May 31st, 2009 on 10:19 pm
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
Tom
June 1st, 2009 on 12:14 am
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
skn3
June 1st, 2009 on 5:47 pm
The most sensible method would be to get your scores out of the database and then do the grouping in php.
jimlife
June 1st, 2009 on 7:56 pm
@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
Tom
June 1st, 2009 on 11:45 pm
Help with comment?
Tom
June 1st, 2009 on 11:46 pm
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
Tom
June 1st, 2009 on 11:49 pm
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
Tom
June 2nd, 2009 on 12:02 am
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
jimmy
June 3rd, 2009 on 5:51 pm
@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
Tom
June 3rd, 2009 on 11:51 pm
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
Jim Zak
July 21st, 2009 on 8:25 am
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
Venn
September 4th, 2009 on 9:21 am
@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;
nay
January 31st, 2010 on 4:10 pm
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:\xampp\htdocs\marketing\table_program\lihat_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
jimmy
March 2nd, 2010 on 5:38 pm
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;
jimmy
March 2nd, 2010 on 5:42 pm
@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)
Jan
April 29th, 2010 on 12:56 pm
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
Patrick Muls
May 21st, 2010 on 1:27 pm
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.
Pete Kitterman
June 1st, 2010 on 12:29 am
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
Gift vincy
June 24th, 2010 on 7:17 am
This post was very useful for me. Nice artical
czeshirecat
August 17th, 2010 on 3:07 pm
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
jimmy
August 21st, 2010 on 3:48 pm
@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?
czeshirecat
August 21st, 2010 on 4:42 pm
I ran it from a mysql .net connector in visual studio c# .net project. It DID run ok in mysql query browser tho.
jimmy
August 30th, 2010 on 11:12 pm
@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