CODEWITHSUNDEEP
phpmysql
Brad Fletcher
Brad Fletcher

Reputation: 3593

SQL/PHP Rank (row number) from table

I have a table of horses and I on the horses profile pages i want to show the global rank of the horse, my table look like this.

id  name       first second third 
282 NewBreed       3      1     0 
278 CueCard        1      1     0 
283 TapTapBoom     1      0     0 
286 Boomboom       0      0     0 
285 Hoe            0      0     0 
284 Boombastic     0      0     0 
287 Haha           0      0     0 
288 Boom           0      0     0 
280 Annie          0      1     0 
279 Boutan         0      1     0 
281 Wahay          0      1     0 
289 42r3etgf       0      0     0

For instance if I was on the profile for the horse "TapTapBoom" his global rank would be 3rd... How do I get this number? i'm guessing I need to get the row number after I have sorted the table by "First".

SELECT *
FROM horses
ORDER BY first
DESC
SELECT row_number
WHERE name = TapTapBoom

So in My PHP I need to input the horse ID or name and as a result I get the horses global rank... the rank is his position of the horse in the table after the table has been sorted by "First" in desc order.

Here is the original table:

enter image description here

Upvotes: 1

Views: 225

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

As MySQL does not have a built in row number function like other RDBMS, you will have to use a variable to achieve this:

SELECT @rank:=@rank+1 AS rank, name, runs
FROM horses, (SELECT @rank=0) vars 
ORDER BY runs DESC, name

This will order by runs in descending order, and in the case of a tie, it will sort the rider names alphabetically in ascending order.

Upvotes: 1

Related Questions