Swim in SQL server

Posts tagged ‘Row_Number’

Working with Window Functions 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