Get startedGet started for free

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

View Course

Exercise instructions

  • Fill in each window function based on the column alias. You should include ROW_NUMBER(), RANK(), and DENSE_RANK() exactly once.
  • Fill in the OVER clause ordering by ir.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;
Edit and Run Code