In this article I will explain the difference between the analytic functions ROW_NUMBER(), RANK() and DENSE_RANK(). These three analytic functions are frequently used by SQL Server Developers.
USE [AdventureWorks]
GO
-- Create tbl_temp Table
CREATE TABLE [dbo].[tbl_temp](
[code] [int] NOT NULL,
[color] [nvarchar](50) NULL,
[type] [nvarchar](50) NULL
)
-- Insert records in the table
GO
INSERT [dbo].[tbl_temp] ([code], [color], [type]) VALUES (1, N'Silver', N'Radio')
GO
INSERT [dbo].[tbl_temp] ([code], [color], [type]) VALUES (2, N'Black', N'Radio')
GO
INSERT [dbo].[tbl_temp] ([code], [color], [type]) VALUES (3, N'Black', N'TV')
GO
INSERT [dbo].[tbl_temp] ([code], [color], [type]) VALUES (4, N'White', N'TV')
GO
INSERT [dbo].[tbl_temp] ([code], [color], [type]) VALUES (5, N'Brown', N'TV')
GO
INSERT [dbo].[tbl_temp] ([code], [color], [type]) VALUES (6, N'Red', N'Phone')
GO
-- See records from the table
SELECT * FROM tbl_temp
-- Row Number, Rank and Dense rank
SELECT *, ROW_NUMBER() OVER(ORDER BY [type]) AS RowNumber,
RANK() OVER(ORDER BY [type]) AS RankValue,
DENSE_RANK() OVER(ORDER BY [type]) AS DenseRank
FROM tbl_temp
