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 |