Mysql - rank field for query
How do I add a field containing the rank of rows in the query result set?
I originally used php to rank my results as it was all on the same page. Now I have added some pages, so now even though I'm on the second page, the first result on the page is still "1st". Obviously I could multiply the page number by the number of results per page and add the result number, but I think there is a better way in the query.
Thanks,
+2
a source to share
1 answer
There are several ways.
- use user-defined variables. it's like playing with fire. Immediate results, but they may not be what you expect or like. see http://code.openark.org/blog/mysql/sql-ranking-without-self-join
-
use GROUP_CONCAT. this works well as long as the set is not too large. See: http://rpbouman.blogspot.com/2009/09/mysql-another-ranking-trick.html
-
with self-connection. see the first link I provided. can be slow though.
+3
a source to share