Skip to main content

Rank Functions in SQL SERVER


1. ROW_NUMBER() OVER ([PARTITION BY CLAUSE] <ORDER BY CLUASE>):

Returns the sequantial number of a row within the a partition of result set at 1 for the first row of the each partition.

2. RANK() OVER ([PARTITION BY CLAUSE] <ORDER BY CLUASE >):

Returns rank for rows within the partition of result set.

3. DENSE_RANK() OVER ([PARTITION BY CLAUSE] <ORDER BY CLUASE >):

Returns rank for rows within the partition of result set.With out any gaps in the ranking.

4. NTILE(INTEGER_EXPRESSION) OVER ([PARTITION BY CLAUSE] <ORDER BY CLUASE >):

Distributes the rows in an ordered partition into a specified number of groups.

Examples:


--create Employee table
create table Employee
(
                EmpId int identity(1,1) primary key,
                FirstName varchar(100),
                LastName varchar(100),
                JoinDate datetime ,
                Salary int ,
                Department varchar(20)
)

--Insert data to Employee table

insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Rakesh','Kalluri','2012-07-01 10:00:00.000',20000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Shabari','Vempati','2011-05-01 10:00:00.000',25000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Venkatesh','Bodupaly','2013-04-01 10:00:00.000',15000,'Bpo')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Surjan','Peddineni','2011-07-01 10:00:00.000',25000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Nani','Ch','2010-07-01 10:00:00.000',50000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Raju','Chinna','2012-07-01 10:00:00.000',25000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Kiran','Kumar','2011-07-01 10:00:00.000',20000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Raki','Kumar','2012-07-01 10:00:00.000',17000,'Bpo')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Sri','Vidya','2011-07-01 10:00:00.000',30000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Fehad','MD','2013-07-01 10:00:00.000',20000,'Bpo')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Anusha','Kumari','2011-07-01 10:00:00.000',35000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Venky','Naidu','2013-07-01 10:00:00.000',20000,'Bpo')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Radha','Kumari','2012-07-01 10:00:00.000',10000,'Bpo')

--selecting data from Employee  table
select * from Employee
  





Row_Number() with out using partition cluase
select * ,row_number() over (order by Salary desc) as Row_Num from Employee


  



Row_Number() with using partition cluase
select * ,row_number() over (partition by Department order by Salary desc) as Row_Num from Employee






rank() with out using partition cluase
select * ,rank() over (order by Salary desc) as [Rank] from Employee






rank() with using partition cluase
select * ,rank() over (partition by Department order by Salary desc) as [Rank] from Employee




dense_rank() with out using partition cluase
select * ,dense_rank() over (order by Salary desc) as [Dense_rank] from Employee





dense_rank() with using partition cluase
select * ,dense_rank() over (partition by  Department order by Salary desc) as [Dense_rank] from Employee




ntile(input_exp) with out using partition cluase
select * ,ntile(3) over (order by Salary desc) as [ntile] from Employee

In Ntile it accepts the input parameter based on input it divides the row ranking.









ntile(input_exp) with using partition cluase
select * ,ntile(3) over (partition by De6partment order by Salary desc) as [ntile] from Employee



Comments

Post a Comment

Popular posts from this blog

SP_MSForeachtable

Some times we need to query on the all the tables in one data base single statement. We use SP_MSForeachtable this is known as undocumented stored procedures . These all are system stored procedures. These stored procedures is place in Master database. NOTE: Please do not run all these queries in Production environment Example: create database UnDocumentedStoredProcedure use UnDocumentedStoredProcedure create table Emp ( ID int identity ( 1 , 1 ), Name varchar ( 50 ), Salary int ) insert into Emp ( Name , Salary ) values ( 'rakesh' , 8000 ),( 'raju' , 9000 ) create table Dept ( ID int identity ( 1 , 1 ), DeptName varchar ( 100 ) ) insert into Dept ( DeptName ) values ( 'CSE' ),( 'IT' ) We are created new database and also created some table with some dummy data. Select all tables data: exec sp_MSForeachtable 'select * from ?...

SET ROWCOUNT

Stop the processing after the number of rows has been affected. Example: Step-1: Create the Table and insert some sample data. CREATE TABLE STUDENT ( ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , STUDENTNAME VARCHAR ( 100 ) NOT NULL , LIVINGLOCATION VARCHAR ( 100 ) NULL ) INSERT INTO STUDENT ( STUDENTNAME , LIVINGLOCATION ) SELECT 'Rakesh' , 'Hyderabad' UNION ALL SELECT 'Raju' , 'Delhi' UNION ALL SELECT 'Madhu' , 'Hyderabad' UNION ALL SELECT 'Naresh' ,NULL UNION ALL SELECT 'Venaktesh' , 'Chennai' In above Insert Statement we inserted 5 sample records. Step-2: Using SET ROWCOUT SET ROWCOUNT 3 ; SELECT * FROM STUDENT ORDER BY ID DESC It will affected 3 rows after that processing has been stopped. Step-2: Using SET ROWCOUT 0 SET ROWCOUNT 0 ; SELECT * FROM STUDENT ORDER BY ID DESC ...