What is Row_Number, CTE , Rank() and Dense_Rank() in Sql Server

What is Row_Number, CTE , Rank() and Dense_Rank() in Sql Server ………………………………

ROW_NUMBER(): This one generates a new row number for every row, regardless of duplicates within a partition.

--Create  database

create database SqlPractice

use sqlpractice

-- create table.....

create table Employee

(

 EmpId varchar(50),

 EmpName varchar(50)

 )

 select * from Employee

Row_Number

-- RowNumber

select ROW_NUMBER() over(order by EmpId ) as RowNo,EmpId,EmpName from Employee

select ROW_NUMBER() over(order by EmpName) as RowNo,EmpId,EmpName from Employee

-- Getting Alternate Rows  From Table.......

--Odd Row from Table

select * from( Select ROW_NUMBER() over (order by EmpId) as [Row. No.],EmpName from Employee) e  where e.[Row. No.] %2=1;

--Even Row from Table

select * from( Select ROW_NUMBER() over (order by EmpId) as [Row. No.],EmpName from Employee) e  where e.[Row. No.] %2=0;

--Row  Between from Table

select * from( Select ROW_NUMBER() over (order by EmpId) as [Row. No.],EmpName from Employee) e  where e.[Row. No.] between 5 and 10

What is CTE (Common Type Expression) in Sql….

CTE are an expression that returns a temporary result set from inside a statement. This result set is similar to a hybrid Derived Table meets declared Temporary Table. The CTE contains elements similar to both. Some of the most frequent uses of a Common Table Expression include creating tables on the fly inside a nested select, and doing recursive queries. CTE can be used for both selects and DML statements.

--For Example …

with result_with_row_no([Row. No.],EmpName)

as

(select ROW_NUMBER() over(order by EmpId) as [Row. No],EmpName from Employee)

select * from result_with_row_no where [Row. No.] % 2 =1

Tempory Table in Sql….

This small example displays several interesting concepts. The CTE is called by name from the SELECT * statement, similar to a Temporary Table. However, if a Temporary Table were used, it would first have to be created, and then populated;

-- with Temporary Tables..............

create table #MyEmployee

(

EmpId varchar(50),

EmpName varchar(50)

)

insert into #MyEmployee

(EmpId,EmpName)

select ROW_NUMBER() over(order by EmpName) as [Row. No],EmpName from Employee where EmpName like 'N%'

-- checked

select * from #MyEmployee

Rank()  and Dense_Rank() in Sql Server…………………………

Rank(): The RANK() function will return the ranking of a set of values within a given partition. Means you can say This one generates a new row number for every row, regardless of duplicates within a partition. Let’s take a look at our sample set of values again: {25, 25, 35, 57, 57, 78}. If we apply the RANK function against this set of values, then we will end up with these rankings: {1,1,3,4,4,5}

Dense_Rank(), if there is a tie then none of the ranks will be skipped. This means that the ranks will remain in consecutive order. . Means you can say This one generates a new row number for every distinct row, leaving no gaps between groups of duplicates within a partition.Let’s take a look at our sample set of values again: {25, 25, 35, 57, 57, 78}. If we apply the DENSE_RANK function against this set of values, then we will end up with these rankings: {1,1,2,3,3,4}.

The difference between DENSE_RANK() and RANK()

The one and only difference between the DENSE_RANK() and RANK() functions is the fact that RANK() will assign non-consecutive ranks to the values in a set in the case of a tie, which means that with RANK() there will be gaps between the integer values when there is a tie. But the DENSE_RANK() will assign consecutive ranks to the values in the case of a tie, so there will be no gaps between the integer values in the case of a tie

create table Student

(

RollNo int primary key,

Name varchar(50),

Marks int

)

insert  into Student values(1,'Ravi Singh',78)

insert  into Student values(2,'S K Tyagi',45)

insert  into Student values(3,'Ravinder Goyel',45)

insert  into Student values(4,'Fahad',20)

-- Rank.....................

select RollNo,Name,Marks,RANK() over(order by Marks desc) [Ranking] from student

-- DENSERANK.................

select RollNo,Name,Marks,DENSE_RANK() over(order by Marks desc) [Ranking] from student

Result :

create table [Order Details]

(

OrderId int,

ProductID int,

UnitPrice money,

Quantity smallint,

Discount real

)

--Rank() => return the rank order of each row with in in the partion order of a result set with  Number of Sold ascending order……

select ProductID,COUNT(orderid )[Number Sold],RANK() over(order by count(orderid)) [Ranking] from [Order Details] group by ProductID order by Ranking,[Number Sold]

--Rank() => return the rank order of each row with in in the partion order of a result set with  Number of Sold descending  order……

select ProductID,COUNT(orderid )[Number Sold],RANK() over(order by count(orderid) desc) [Ranking] from [Order Details] group by ProductID order by Ranking,[Number Sold]

--Dense_Rank() => return the rank order of each row with in in the partion order of a result set with  Number of Sold ascending order……

select ProductID,COUNT(orderid )[Number Sold],dense_RANK() over(order by count(orderid) desc) [Ranking] from [Order Details] group by ProductID order by Ranking,[Number Sold]

Result :

Read More