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
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
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";
Advertisement
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"
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