I have a set of records for employee utilization, and I wanted a quick way to rank them from highest utilization to lowest utilization. It turns out that the ROWNUMBER() function did exactly what I needed.
Here is an example of my data:
ConsultantName | UtilizationPercent |
Consultant A | 100.5% |
Consultant B | 87.4% |
Consultant C | 95.0% |
Consultant D | 101.2% |
The following query can be used to return the results of my data by ranking:
SELECT
ConsultantName
UtilizationPercent,
ROW_NUMBER() OVER (ORDER BY UtilizationPercent DESC) AS ConsultantRanking
FROM
ConsultantUtilization
ORDER BY
ConsultantRanking
This query returns the following:
ConsultantName | UtilizationPercent | ConsultantRanking |
Consultant D | 101.2% | 1 |
Consultant A | 100.5% | 2 |
Consultant C | 95.0% | 3 |
Consultant B | 87.4% | 4 |