What is DENSE_RANK(),Rank(),ROW_NUMBER in Sql?

-> Create a Table BlogCount

CREATE TABLE [dbo].[BlogCount](
[BloggerName] [varchar](10) NULL,
[Topic] [varchar](15) NULL,
[Year] [int] NULL,
[Total] [int] NULL
)

->Inserting Data

INSERT [dbo].[BlogCount] ([BloggerName], [Topic], [Year], [Total]) VALUES (N'Ritesh', N'SQL', 2006, 17)
INSERT [dbo].[BlogCount] ([BloggerName], [Topic], [Year], [Total]) VALUES (N'Ritesh', N'SQL', 2007, 124)
INSERT [dbo].[BlogCount] ([BloggerName], [Topic], [Year], [Total]) VALUES (N'Ritesh', N'SQL', 2008, 124)
INSERT [dbo].[BlogCount] ([BloggerName], [Topic], [Year], [Total]) VALUES (N'Ritesh', N'.NET', 2008, 24)
INSERT [dbo].[BlogCount] ([BloggerName], [Topic], [Year], [Total]) VALUES (N'Alka', N'SQL', 2007, 14)
INSERT [dbo].[BlogCount] ([BloggerName], [Topic], [Year], [Total]) VALUES (N'Alka', N'.NET', 2007, 18)
INSERT [dbo].[BlogCount] ([BloggerName], [Topic], [Year], [Total]) VALUES (N'Alka', N'SQL', 2008, 14)


->Now write a Querry.

Select * from BlogCount

->This will show you all the data.

BloggerName	Topic	Year	Total
Ritesh	SQL	2006	17
Ritesh	SQL	2007	124
Ritesh	SQL	2008	124
Ritesh	.NET	2008	24
Alka	SQL	2007	14
Alka	.NET	2007	18
Alka	SQL	2008	14

->Now Lets Apply the DENSE_RANK and Partition in Querry.

SELECT BloggerName,Topic,[Year],Total,DENSE_RANK()  OVER (Partition by BloggerName Order by Total DESC) as 'Ranking'
FROM
BlogCount

BloggerName	Topic	Year	Total	Ranking
Alka	.NET	2007	18	1
Alka	SQL	2008	14	2
Alka	SQL	2007	14	2
Ritesh	SQL	2007	124	1
Ritesh	SQL	2008	124	1
Ritesh	.Net	2008	24	2
Ritesh	SQL	2006	17	3

->Now Lets Apply RANK instead of DENSE_RANK.

SELECT BloggerName,Topic,[Year],Total,RANK()  OVER (Partition by BloggerName Order by Total DESC) as 'Ranking' FROM BlogCount

BloggerName	Topic	Year	Total	Ranking
Alka	.NET	2007	18	1
Alka	SQL	2008	14	2
Alka	SQL	2007	14	2
Ritesh	SQL	2007	124	1
Ritesh	SQL	2008	124	1
Ritesh	.Net	2008	24	3
Ritesh	SQL	2006	17	4

->From Above Two Querry what is the difference between Two DENSE_RANK and RANK?
  In Rank() function if there is two same Rank  then the next rank will be not immediate after the same rank but its number of that record..for example if there are two records of the same Rank say 1 then the next rank will be the 3 and so on.while in DENSE_RANK it will be next rank.means if there are two record of Rank 1 then the next rank will be the 2.Just check the above two examples and see the output you will get clear Idea.


ROW_NUMBER:It will Return the RowNumber.

Select * from BlogCount

BloggerName	Topic	Year	Total
Ritesh	SQL	2006	17
Ritesh	SQL	2007	124
Ritesh	SQL	2008	124
Ritesh	.NET	2008	24
Alka	SQL	2007	14
Alka	.NET	2007	18
Alka	SQL	2008	14

SELECT ROW_NUMBER() OVER (
   ORDER BY Total) AS RowNumber,
BloggerName, Topic,[Year],Total
FROM BlogCount


Output

RowNumber	BloggerName	Topic	Year	Total
1	Alka	SQL	2007	14
2	Alka	SQL	2008	14
3	Ritesh	SQL	2006	14
4	Alka	.Net	2007	18
5	Ritesh	.Net	2008	24
6	Ritesh	SQL	2007	124
7	Ritesh	SQL	2008	124