PHP MYSQL finding row number/rank from table with order by clause

Question

This has been doing my head in for hours now. It seems so simple but i just cant find the answers.

Basically I have a MYSQL table of the following example data, NOTE there are other columns but are not necessary for this question.

id | x  | y  
1  | 50 | 3
2  | 40 | 1
3  | 50 | 0
4  | 50 | 1
5  | 40 | 2

I want to order the table by x DESC and secondly y DESC. So I have this:

SELECT id, x, y FROM table_name ORDER BY x DESC, y DESC

Hopefully resulting in this:

id | x  | y  
1  | 50 | 3
4  | 50 | 1
3  | 50 | 0
5  | 40 | 2
2  | 40 | 1

Then given a specific id stored in a variable, I want to be able to find what row number its found in given the query. ie Find where it is ranked amongst the other rows.
I know there is a RANK and DENSE_RANK, if I need to use either I need RANK. But if using that's not the best solution, or how to use RANK please help.

For the above data if my id variable $id = 4, it's rank would be 2 as it is sorted second. And i need this value returned as a variable. Thanks

possible duplicate of [Mysql rank function](http://stackoverflow.com/questions/3333665/mysql-rank-function) — John Conde, Jan 11 '14 at 14:46

Gordon Linoff · Accepted Answer · 2014-01-11T16:45:11.580

3

MySQL does not support window/analytic functions. You can emulate it using variables or complicated subqueries. In your case, I think this is what you want:

SELECT id, x, y, (@rn := @rn + 1) as rank
FROM table_name cross join
     (select @rn := 0) const
ORDER BY x DESC, y DESC;

EDIT:

If you just want the result for a given value, you can count the number of rows using an aggregation query:

select count(*)
from table_name t cross join
     (select t.*
      from table_name
      where id = $id
     ) id
where t.x > id.x or (t.x = id.x and t.y >= id.y)

EDIT II:

To use the first query to get a single id, put it in a subquery:

select *
from (SELECT id, x, y, (@rn := @rn + 1) as rank
      FROM table_name cross join
           (select @rn := 0) const
      ORDER BY x DESC, y DESC
     ) t
where id = $id;

edited Jan 11 '14 at 16:45

answered Jan 11 '14 at 14:50

Gordon Linoff

1,242,037
58
646
786

Your first example spits out a table of results exactly what Im after, but how do I then query that to find the row where $id = id and get that rows rank value. Your second example didnt seem to work. Thanks – jaketyler27 Jan 11 '14 at 16:20
You sir are a genius, thanks a lot for your help! If you're ever in Australia I'll buy you a beer :) – jaketyler27 Jan 12 '14 at 02:21

score 0 · Answer 2 · answered Jan 11 '14 at 14:50

A simple count should do the trick:

SELECT count(1)+1 FROM table_name WHERE x < ? or (x = ? and y > ?)

Where the question marks should be filled with the value of the column x of the row with id = $id. The +1 is because it counts the rows above the record, meaning that the rank = count+1.

PHP MYSQL finding row number/rank from table with order by clause

2 Answers2