CODEWITHSUNDEEP
sqlsql-server
Matt
Matt

Reputation: 1570

Select percentile rank from table based on score SQL

I have a table that contains what score equates to what percentile rank. This is a snippet from said table to give you an idea of structure:

dateStart   dateEnd     category    score   percentileRank
2014-10-01  2014-12-31  category1   81.5    97.0
2014-10-01  2014-12-31  category1   80.7    96.0
2014-10-01  2014-12-31  category1   80.0    95.0
2014-10-01  2014-12-31  category1   79.5    94.0
2014-10-01  2014-12-31  category1   79.0    93.0
2014-10-01  2014-12-31  category1   78.6    92.0
2014-10-01  2014-12-31  category1   78.1    91.0
2014-10-01  2014-12-31  category1   77.8    90.0
2014-10-01  2014-12-31  category1   77.6    89.0
2014-10-01  2014-12-31  category1   77.3    88.0
...         ...         ...         ...     ...

How do I SELECT the percentileRank based on a given score? For example, if the score was 81, it should tell me that the percentile rank was 96. Since the score can be any decimal value, I need to account for the ranges of a given percentile rank. As such, I'm having a bit of trouble working out the conditions for this in an efficient manner.

Upvotes: 0

Views: 91

Answers (1)

Joe Enos
Joe Enos

Reputation: 40413

Based on your example, a top or max query will give you the closest match down from your input:

select top 1 percentileRank
from tbl
where score <= @score
order by score desc;

select max(percentileRank)
from tbl
where score <= @score;

Upvotes: 2

Related Questions