Contrasting ROW_NUMBER(), RANK(), and DENSE_RANK()
Among the ranking window functions, ROW_NUMBER()
is the most common, followed by RANK()
and DENSE_RANK()
. Each of these ranking functions (as well as NTILE()
) provides us with a different way to rank records in SQL Server.
In this exercise, we would like to determine how frequently each we see incident type 3 in our data set. We would like to rank the number of incidents in descending order, such that the date with the highest number of incidents has a row number, rank, and dense rank of 1, and so on. To make it easier to follow, we will only include dates with at least 8 incidents.
This exercise is part of the course
Time Series Analysis in SQL Server
Exercise instructions
- Fill in each window function based on the column alias. You should include
ROW_NUMBER()
,RANK()
, andDENSE_RANK()
exactly once. - Fill in the
OVER
clause ordering byir.NumberOfIncidents
in descending order.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
ir.IncidentDate,
ir.NumberOfIncidents,
-- Fill in each window function and ordering
-- Note that all of these are in descending order!
___() OVER (___ ___ ir.NumberOfIncidents ___) AS rownum,
___() OVER (___ ___ ir.NumberOfIncidents ___) AS rk,
___() OVER (___ ___ ir.NumberOfIncidents ___) AS dr
FROM dbo.IncidentRollup ir
WHERE
ir.IncidentTypeID = 3
AND ir.NumberOfIncidents >= 8
ORDER BY
ir.NumberOfIncidents DESC;