[CLUE-Tech] Oracle rownum; mysql equivalent

[Jeff Cann]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

OK.  I have a dynamic report that lists a set in order of points from a table:

Player	Points
A		1000
B		900
C		1100	

What I want is a MySql query that will add a psuedo column, called rank.  The 
'rank' column simply increments with each row, e.g.,

Rank	Player	Points
1    		C		1100
2		A		1000
3		B		900

The ranking pseudo column is dynamically generated when the SQL statement runs 
as the ranking of a player can change at any time.

Oracle has something called rownum that is a pseudo column.  So from Oracle, I 
would do this:

select rownum, player, points from table order by points asc;

Here's the MySql equivalent.  Use a user variable and then increment it.

mysql> SET @rownum := 0;
mysql> SELECT @rownum := @rownum + 1 AS rank, score
    -> FROM t ORDER BY score DESC;

- From :
+ http://db.oreilly.com/recipe/solution4.html

It took me a good hour to find this out, so I thought I would post it for 
future (easier) reference.

Jeff
- -- 
"Faith that does not affect a person's culture is a faith not fully embraced, 
not entirely thought out, not faithfully lived."
- - Pope John Paul II

http://isuma.org/


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFBa1dvi4b9OApLCmoRAj9vAJ0X50zONYiAhch0jp/v/fcI/VZkGgCfaMUK
pTMlgaWcGPkb8SfLc3Uodzw=
=L3ca
-----END PGP SIGNATURE-----