Thursday 27 November 2014

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 :


1 comments:

  1. Sir ,, please tell me how to store data in database in hindi font and how to type in hindi in textbox.... While using web application .... like if we use sign up form then i want to type in hindi in Name textbox.....please help me...

    ReplyDelete