CODEWITHSUNDEEP
mysql
SUN
SUN

Reputation: 973

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?

Upvotes: 1

Views: 440

Answers (2)

Daniel W.
Daniel W.

Reputation: 32280

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

Upvotes: 2

Chris Pickford
Chris Pickford

Reputation: 8991

Wrap in a subquery.

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

Upvotes: 1

Related Questions