1) Row_Number() -- To get the Unique order of Rows
2) Rank() – To rank the Records with skipped Rows
3) Dense_Rank() – To rank the Records without skipping any Rows
Lets prepare base for these:
1) Create a table like this:
CREATE TABLE [dbo].[Student](
[StudentID] [int] IDENTITY(1,1) NOT NULL,
[StudentName] [varchar](50) NULL,
[Age] [int] NULL
) ON [PRIMARY]
2) Insert data into that like this:
3) Now run the below Query:
SELECT *
,ROW_NUMBER() OVER (ORDER BY age DESC) [Unique Order]
,RANK() OVER (ORDER BY age DESC) [Skipped Order]
,DENSE_RANK() OVER (ORDER BY age DESC) [Multiple Order]
FROM student
If u closely look at the above result set [Unique Order] column represent the Unique row number of each row which we got using Row_Number().
[Skipped Order] column represent the order of Rank which skips the number. This may be use in situation where u need to find out the rank of student based on marks, or rank of participants based on points..
[Multiple Order] column represents the intelligent Rank which does not skip the number. This may be used in situation where u need to select employees based on their salaries.
Difference between Rank() and Dense_Rank() is while assigning the next rank (after the common values rows) to the row RANK will consider the total count of rows before that row and DESNE_RANK will just give next rank according to the value.
I hope u understand what I tried to say..
Comments