Guruji Point - Code You Want To Write

  • Home
  • Asp.Net
  • WCF
  • SQL
  • Type Script
  • AngularJS
  • Tech News
  • Blog
  • Earn Online

Thursday, 20 April 2017

Difference Between Rank(), Dense_Rank() And Row_Number() In SQL

 dense_rank in sql, Dense_Rank() in Sql, denserank and rownumber. Use of rank(), Rank in sql, row_number in sql. Difference between rank, row_number() and dense_rank(), what is Rank()     No comments   


Introduction
In this post we will learn what is the difference between Rank(), Dense_Rank() And Row_Number() and when do we need this.

Previous Updates
In previous articles we have what is Pivot table in SQl. Difference between  Scope_Identity(), @@Identity and Ident_Current . Stuff and Replace in SQl. Temp table and table variable difference and when to use. Sequence in sql server with example.Group By in SQL Server and its use. If Else and Case statement  , While Loop . What is Cursor in sql and use of cursor.

Practice
For to understand all three SQL function first we need a table and few records for that table. Here i am sharing my table structure with you :-

CREATE TABLE [dbo].[TestTable] (
    [ID]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (50) NULL,
    [Marks] INT NOT NULL
);

Now Here is my DataScript for values insertion
INSERT INTO TestTable VALUES( 'Jack' ,90)
INSERT INTO TestTable VALUES( 'Nicks' ,70)
INSERT INTO TestTable VALUES( 'GN' ,80)
INSERT INTO TestTable VALUES( 'Shiv' ,65)

How to use Row_Number(), Rank() and Dense_Rank() In Sql

Now its time to write the all three sql function to use them and know what is the use of these Rank(), Dense_Rank() and Row_Number().

SELECT Name,
              Marks,
ROW_NUMBER () OVER (ORDER BY Marks DESC) 
  as ROW_NUMBER,
RANK () OVER (ORDER BY Marks DESC) 
  as RANK,
DENSE_RANK () OVER (ORDER BY Marks DESC)
 as DENSE_RANK
FROM dbo.TestTable

After running this expression  you will get the following output


Little bit confusing now. After seeing the result we find all three function returning the same values 1,2,3,4 . So whats the main difference in this. 
Now i am duplication few records for better understanding on this topic. 



In Select table statement you can see few records with same Marks like 90 and 70 and after that when we perform the same query expression on Marks then we find the output which we really wants.

Explanation Based Upon the Result

Row_Number()
It plays a important role in sql server .Row_Number() returns a unique number for each row starting with 1. 

When we have large records and we have to get few records related to some row then we use this function.

Rank()
Rank() function will assign a unique value to each distinct row but it leaves a gap between the group records.
Means it will display the same Rank for all duplicate records (just like Jack and GN  Rank is 1 and Nicks , Mark, Roman Rank is 3) .
It leaves a gap between the group records means here 'Jack' and 'GN' has same Rank 1 and after this it assigns rank 3 to Nicks, implies it count no of occurrences of Marks '90' and it gets count 2 then the next Rank will be Occurrence Count + 1 which is '3'.

Dense_Rank()
It is similar to Rank function but it will not leave a gap between the group elements. In other words w e can say that Dense_Rank() function will assign a unique value to each distinct row.

  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to XShare to Facebook
Newer Post Older Post Home

0 comments:

Post a Comment

Facebook Updates

Guruji Point

Categories

Comman table Expression in SQL Dynamic Grid Row IQueryable in c# Learn AngularJS Scope_Identity() Use of indexes comma seperated string in sql row_number() and dense_rank() salary table structure. while loop in sql why do we need cursor why tuple used

About Us

This blog is about the Programming ,Tech News and some intresting facts related Contents. Here you can find fundamental and expert level topic of programming languages very practically related to C# , Asp.Net ,Sql-Server, JavaScript, Jquery, WebServices And also Web-Api, WCF ,WPF, Angular Js.

Contact Us

Email Us - gurujipoints@gmail.com
Contact- 8077657477

Reach Us

Copyright © Guruji Point - Code You Want To Write