Swim in SQL server

There are 4 Types of Rank functions in SQL Server.
Row_Number() : Row Number will return serial numbers in the result set.
Rank()                   : Rank will returns the rank of each row , if the values are same for more records it will give the same number,if the value is change, then it will give the row number in the partition.
Dense_Rank()    : Dense_Rank will returns the rank of each row,if the values are same, then it will give the same number,if the value is changed ,then it will give the next number of the previous row in the partition.
Ntile(n)                : It will partition the result set into “n” numbers and give the rank to each record.
–Declare a table with name of @CLASSROOM
DECLARE @CLASSROOM TABLE (ID INT IDENTITY, NAME VARCHAR(100) , BRANCH VARCHAR(10) , M1 INT , M2 INT , M3 INT , TOT as (M1+M2+M3) )
–Insert values into @CLASSROOM
INSERT INTO @CLASSROOM
VALUES ( ‘RAJ’ ,‘CSE’ , 60 , 70 , 40 )
              ,( ‘RAHUL’ ,‘CSE’ , 60 , 80 , 30 )
              ,( ‘RAMESH’ ,‘CSE’ , 60 , 60 , 40 )
              ,( ‘RAKESH’ ,‘IT’ , 50 , 70 , 60 )
              ,( ‘RAM’ ,‘IT’ , 50 , 70 , 60 )
              ,( ‘RAVI’ ,‘IT’ , 60 , 70 , 90 )
              ,( ‘RAJESH’ ,‘IT’ , 70 , 30 , 80 )
              ,( ‘ROHITH’ ,‘CSE’ , 50 , 60 , 90 )
              ,( ‘ROCK’ ,‘IT’ , 50 , 70 , 50 )
              ,( ‘RAAGHAV’ ,‘CSE’ , 50 , 60 , 60 )
 
–Ranking functions
SELECT NAME , TOT
              ,ROW_NUMBER() OVER(ORDER BY TOT DESC) [Row_Number]
              ,RANK() OVER(ORDER BY TOT DESC) [Rank]
              ,DENSE_RANK() OVER( ORDER BY TOT DESC) [Dense_Rank]
              ,NTILE(2) OVER(ORDER BY TOT DESC) [Ntile]
FROM @CLASSROOM
 
–Ranking functions With Partition By clause.
SELECT BRANCH,NAME , TOT
              ,ROW_NUMBER() OVER(PARTITION BY BRANCH ORDER BY TOT DESC) [Row_Number]
              ,RANK() OVER(PARTITION BY BRANCH ORDER BY TOT DESC) [Rank]
              ,DENSE_RANK() OVER(PARTITION BY BRANCH ORDER BY TOT DESC) [Dense_Rank]
              ,NTILE(2) OVER(PARTITION BY BRANCH ORDER BY TOT DESC) [Ntile]
FROM @CLASSROOM
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: