Using RANK(), and ROW_NUMBER to Rank a field without ‘skipping’ numbers in SQL Server & Teradata

In this post, I want to cover off a little bit of RANK() usage in SQL, but then focus on how to use ranking to get a unique rank number for each row. I will cover both SQL Server and Teradata.

RANK() is pretty simple to use, and works pretty much the same way in Teradata & SQL Server

SELECT
Region, Store, Sales
,RANK() OVER (ORDER BY sales DESC) AS SalesRank
FROM SalesTable

This will list your stores, their sales and the sales rank for each store. Now, lets say we want to show the ranking by Region, and perhaps only want to see the top 4 stores in each region. You ‘Partiton’ your data, and ‘Qualify’ the results

SELECT
Region, Store, Sales
,RANK() OVER (PARTITION BY Region ORDER BY sales DESC) AS SalesRank
FROM SalesTable
QUALIFY SalesRank >= 4

Using Rank, if you have multiple entries with the same value (eg 2 stores with the same sales value), rank will give them the same rank number. The next store in the list will then skip to the next one.

Sometimes, this is fine and is what we want. But what if it’s not? What if we always want to see 1-4? We can use ROW_NUMBER(). This function works in exactly the same way, but always numbers each record uniquely.

SELECT
Region, Store, Sales
,ROW_NUMBER() OVER (PARTITION BY Region ORDER BY sales DESC) AS SalesRank
FROM SalesTable
QUALIFY SalesRank >= 4

Incidentally, SQL Server (unsure about similar functionality in Teradata) also provides the function DENSE_RANK().

With DENSE_RANK it will never ‘skip’ a number, but will still assign the same number to identical entries.

SELECT
Region, Store, Sales
,Dense_Rank() OVER (PARTITION BY Region ORDER BY sales DESC) AS SalesRank
FROM SalesTable

David

2 comments