DENSE_RANK() function in MS SQL Server
Category: IT, MS SQL Server, SQL Tags: IT, Microsoft, SQL, SSMS Comments: 0 »As we can read in Books Online in SQL Server
Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.
I have created simple example for usage of the Ranking function DENSE_RANK(). We have a database with the addresses of all the residents of a city. Many of them have changed their addresses several times. They have changed them by using web application, because the authorities have decided that’s the most convenient manner for everybody. Down the years, one user may have more then one record (change of address). We need to select only one actual address or only one precedent address per user. We need to partition the table and select only the data we need.
First I create the tables, keys and I populate them.
USE zlika
GO
-- Create Tables and keys
CREATE TABLE [Users]
(
[ID] INT IDENTITY (1, 1) PRIMARY KEY
, [FirstName] VARCHAR(50)
, [LastName] VARCHAR(50)
)
GO
CREATE TABLE [Address]
(
[ID] INT IDENTITY (1, 1) PRIMARY KEY
, [UserID] INT
, [StreetNo] INT
, [StreetName] VARCHAR(100)
, [City] VARCHAR(100)
, [Phone] VARCHAR(25)
, [DateOfRegistration] DATETIME NULL
)
GO
ALTER TABLE [Address]
WITH CHECK ADD CONSTRAINT
FK_Users_ID FOREIGN KEY
(
UserID
)
REFERENCES Users
(
ID
)
GO
ALTER TABLE [Address]
ADD CONSTRAINT DF_Address_DateOfRegistration
DEFAULT (GETDATE())
FOR DateOfRegistration
GO
-- Populate Tables
INSERT INTO [Users] (FirstName, LastName) VALUES
('First', 'Smith')
, ('Second', 'George')
, ('Third', 'Thompson')
GO
SELECT * FROM Users
GO
INSERT INTO [Address] (UserID, StreetNo, StreetName, City, Phone) VALUES (1, 111, 'StreetName1', 'City1', 11111)
WAITFOR DELAY '00:00:01'
INSERT INTO [Address] (UserID, StreetNo, StreetName, City, Phone) VALUES (1, 222, 'StreetName2', 'City2', 22222)
WAITFOR DELAY '00:00:01'
INSERT INTO [Address] (UserID, StreetNo, StreetName, City, Phone) VALUES (1, 333, 'StreetName3', 'City3', 33333)
WAITFOR DELAY '00:00:01'
INSERT INTO [Address] (UserID, StreetNo, StreetName, City, Phone) VALUES (1, 444, 'StreetName4', 'City4', 44444)
WAITFOR DELAY '00:00:01'
INSERT INTO [Address] (UserID, StreetNo, StreetName, City, Phone) VALUES (1, 555, 'StreetName5', 'City5', 55555)
WAITFOR DELAY '00:00:01'
INSERT INTO [Address] (UserID, StreetNo, StreetName, City, Phone) VALUES (2, 1111, 'StreetName11', 'City11', 1111111111)
WAITFOR DELAY '00:00:01'
INSERT INTO [Address] (UserID, StreetNo, StreetName, City, Phone) VALUES (2, 2222, 'StreetName22', 'City22', 2222222222)
WAITFOR DELAY '00:00:01'
INSERT INTO [Address] (UserID, StreetNo, StreetName, City, Phone) VALUES (3, 3, 'StreetName333', 'City333', 333)
GO
SELECT * FROM [Address]
GO
Users table contains three rows as follow:
Users Table
Address table contains eight rows for these three users:
Address Table
We need to partition the table. That means we need to split the data according to some logic. DANSE_RANK() function does it for us. It split users by their ID (UserID), order the partitions descending by date and adds ranks.
SELECT A.ID , A.UserID , U.FirstName , U.LastName , A.StreetNo , A.StreetName , A.City , A.Phone , A.DateOfRegistration , DENSE_RANK() OVER (PARTITION BY UserID ORDER BY DateOfRegistration DESC) AS [Rank] FROM [Address] AS A INNER JOIN dbo.[Users] AS U ON A.UserID = U.ID --WHERE -- [Rank] = 2 GO
The result of the query is the data plus a temporary column for ranking.
Adding DENSE_RANK column (click on image)
If I try to reach the result in single query (uncommenting lines 18 and 19), I’ll get “Invalid column name ‘Rank’” error. The reason is that I generate the [Rank] column during the selection of the data and I can’t filter it at this time.
Invalid column name 'Rank'
To get the data that I need, I simulate a View – create a temporary table and read a filtered data from it.
USE zlika GO CREATE TABLE #Temp ( ID INT , [UserID] INT , [FirstName] VARCHAR(50) , [LastName] VARCHAR(50) , [StreetNo] INT , [StreetName] VARCHAR(100) , [City] VARCHAR(100) , [Phone] VARCHAR(25) , [DateOfRegistration] DATETIME NULL , [Rank] INT ) GO INSERT INTO #Temp SELECT A.ID , A.UserID , U.FirstName , U.LastName , A.StreetNo , A.StreetName , A.City , A.Phone , DateOfRegistration, DENSE_RANK() OVER (PARTITION BY UserID ORDER BY DateOfRegistration DESC) AS [Rank] FROM [Address] AS A INNER JOIN dbo.[Users] AS U ON A.UserID = U.ID GO SELECT ID , UserID , FirstName , LastName , StreetNo , StreetName , City , Phone , DateOfRegistration FROM #Temp WHERE [Rank] = 1 ORDER BY UserID GO DROP TABLE #Temp GO
The result is the last address of the residents. If we need the precedent addresses, we’ll filter the [Rank] column to equals 2.
DENSE_RANK result
Félicitations
