Mysql - Rank field for a query

Question

How can I add a field that contains a rows rank within the result set of a query?

Initially I used php to rank my results as it was all on a single page. Now I have added multiple pages so now even though I am 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 imagine there is a better way within the query.

Thanks,

Answer 1

There are couple of ways.

• use user defined variables. this is like playing with fire. Immediate results but they might not be the ones you expect nor like. see: http://code.openark.org/blog/mysql/sql-ranking-without-self-join

• use GROUP_CONCAT. this works nice as long as the sett isn't too large. See: http://rpbouman.blogspot.com/2009/09/mysql-another-ranking-trick.html

• with self join. see the first link i provided. might be slow though.