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
[…]Using ROW_NUMBER to Rank a field without ‘skipping’ numbers in SQL Server & Teradata | System Secrets[…]