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 :
.jpg)
