Basic ranking of MySQL data and printing result with php

I have a table which in which I want to rank rows where one of the columns equals a value I have defined.

For example in this table I want to get all rows where Col1 = a, then find what is the rank of the row in which Col3 = Ross (ranked by score in Col2).

Col1 | Col2 | Col3
------------------
a    | 10   | John
a    | 6    | Nick
a    | 8    | Ross
a    | 2    | Tim
a    | 4    | Paul
b    | 9    | John
b    | 3    | Nick
b    | 5    | Ross
b    | 7    | Tim
b    | 1    | Paul

JavaScript
​x
 
Col1 | Col2 | Col3------------------a    | 10   | Johna    | 6    | Nicka    | 8    | Rossa    | 2    | Tima    | 4    | Paulb    | 9    | Johnb    | 3    | Nickb    | 5    | Rossb    | 7    | Timb    | 1    | Paul​

Ultimately I want to calculate this:

Col1 | Col2 | Col3 | Rank
-------------------------
a    | 10   | John | 1
a    | 8    | Ross | 2
a    | 6    | Nick | 3
a    | 4    | Paul | 4
a    | 2    | Tim  | 5

JavaScript
 
Col1 | Col2 | Col3 | Rank-------------------------a    | 10   | John | 1a    | 8    | Ross | 2a    | 6    | Nick | 3a    | 4    | Paul | 4a    | 2    | Tim  | 5​

And print the result “2”.

I can sort the table with this query, but dont; know how to then print the result I need (with php).

$query = "SELECT * FROM exampleTable WHERE Col1 = a order by Col2 DESC";

JavaScript
 
$query = "SELECT * FROM exampleTable WHERE Col1 = a order by Col2 DESC";​

Answer

Try this:

"SET @rank=0; SELECT * FROM (SELECT *, @rank:=@rank+1 AS Rank FROM exampleTable WHERE Col1 = 'a' order by Col2 DESC) AS t"

JavaScript
 
"SET @rank=0; SELECT * FROM (SELECT *, @rank:=@rank+1 AS Rank FROM exampleTable WHERE Col1 = 'a' order by Col2 DESC) AS t"​

Here is a test run that shows it working.

If you want to find Ross’s rank you can run the query with WHERE Col3 = 'Ross' added and only the rank column selected, as demonstrated here

Advertisement

Answer