SUN SUN - 5 months ago 8
SQL Question

MySQL get rank from particular row ID

I have list of hospitals under that there are average ratings already calculated. Now I wanted to calculate rank for list of hospitals according to their average ratings from following query

SELECT name,
hospitalID,
currentAvgRating,
@curRank := @curRank + 1 AS rank
FROM hospitals h, (SELECT @curRank := 0) r
ORDER BY currentAvgRating DESC


Now above query works when I want to see all hospitals from table but when I apply WHERE clause like below then result is wrong since with this it takes row position.

SELECT name,
hospitalID,
currentAvgRating,
@curRank := @curRank + 1 AS rank
FROM hospitals h, (SELECT @curRank := 0) r where hospitalID = '453085'
ORDER BY currentAvgRating DESC


Is there any way to get correct result when we apply where clause?

Answer

If you proceed what you just found out, logically ("when there is only 1 listitem, it cannot be ordered") - you will come to the conclusion that you NEED to select ALL rows. But nothing wrong with that, you can pack them into a subselect (which isnt even an expensive one) and apply the WHERE to that:

SELECT * FROM (
  SELECT    name,
            hospitalID,
            currentAvgRating,
            @curRank := @curRank + 1 AS rank
  FROM      hospitals h, (SELECT @curRank := 0) r
  ORDER BY  currentAvgRating DESC
) toplist
WHERE toplist.hospitalID = 453085