I have not written about this subject for long time, as I strongly believe that Book On Line explains this concept very well. SQL Server 2005 has total of 4 ranking function. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.
ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.
DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.
NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)
Distributes the rows in an ordered partition into a specified number of groups.
All the above definition and syntax are taken from BOL. It is difficult to explain above function anything else than what they are explained in BOL. Following example is excellent example from BOL again. This function explains usage of all the four function together in one query.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
,ROW_NUMBER() OVER (
ORDER BY a.PostalCode) AS 'Row Number'
,RANK() OVER (
ORDER BY a.PostalCode) AS 'Rank'
,DENSE_RANK() OVER (
ORDER BY a.PostalCode) AS 'Dense Rank'
,NTILE(4) OVER (
ORDER BY a.PostalCode) AS 'Quartile'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
Resultset:
Most of the content of this article is taken from BOL.
Reference : Pinal Dave (https://blog.sqlauthority.com), ROW_NUMBER, RANK, DENSE_RANK, NTILE
66 Comments. Leave new
Hi Shilpa,
ROW_NUMBER(), RANK() & DENSE_RANK() will produce the same result when the column on which we are doing the sorting (order by) have only unique value.
Example: In above example if Postal Code column fro,m Adress table have only unique entries then ROW_NUMBER(), RANK() & DENSE_RANK() will produce the same result.
how to write a function for generate uniqe employee id
ex,, emp-kk-0001,emp-kk-0002,,Function?
have two columns one with identity property and another column which is derived from it
create table test
(
id int identity(1,1) not null,
employee_id as ’emp-kk-‘+right(‘0000’+cast(id as varchar(10)),4)
)
What’s great is that urban clothing is already really cheap to buy. Knowing the growing amount of customers who have transferred from actual store shopping to online shop buying, you can make a presumption as to how your online business should react to the trend.. ralph lauren http://www.likeralphlaurenpolo.co.uk%2
How can I make two column out of one. That is, Range_From and Range_To for the column SalesPersonID.
For example if I’v 500 Sales Persons working on different days. Data are:
Working of Sale Persons
———————–
SalePersonID SaleDate
001 2012-01-01
002 2012-01-01
003 2012-01-01
004 2012-01-01
.
.
150 2012-01-01
151 2012-01-02
152 2012-01-02
.
.
160 2012-01-02
161 2012-01-03
164 2012-01-04
165 2012-01-04
.
.
500 2012-01-04
I want to have result like:
Working of Sale Persons
———————–
Range_From Range_To SaleDate
001 150 2012-01-01
151 160 2012-01-02
161 161 2012-01-03
162 500 2012-01-04
Very good explanation ,Thanks.
I need the Email_Id’s from the Data column to be sorted in asc order.
who to use DENSE_RANK() for the following table
Considering
COL A COL B RANK
1 91 1
2 92 1
3 93 6
3 94 5
3 95 4
3 96 3
3 97 2
3 98 1
4 99 2
4 100 1
5 101 1
Now i want to display just those columns which as RANK=1, there should not be rank 2 or 3
that is result should be like this
COL A COL B RANK
1 91 1
2 92 1
5 101 1
thanks in adv.
Here’s the code for AdventureWorks2012
USE AdventureWorks2012;
GO
SELECT c.FirstName, c.LastName
,ROW_NUMBER() OVER (
ORDER BY a.PostalCode) AS ‘Row Number’
,RANK() OVER (
ORDER BY a.PostalCode) AS ‘Rank’
,DENSE_RANK() OVER (
ORDER BY a.PostalCode) AS ‘Dense Rank’
,NTILE(4) OVER (
ORDER BY a.PostalCode) AS ‘Quartile’
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Person c
ON s.BusinessEntityID = c.BusinessEntityID
INNER JOIN Person.Address a
ON a.AddressID = c.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD 0;
Hi I have requirement like this
I have table as below
Animal Outlet No
chickens 101
chickens 102
Cows 103
Horses 104
Horses 105
Horses 106
I want to ammend the animal name as
Animal Outlet No
chickens_1 101
chickens_2 102
Cows 103
Horses_1 104
Horses_2 105
Horses_3 106
Pleas help me to resolve this
Thanks
bhagya
drop table #test
create table #test (Animal varchar(100), Outlet_No bigint)
insert into #test values
(‘chickens’, 101),
(‘chickens’, 102),
(‘Cows’, 103),
(‘Horses’, 104),
(‘Horses’, 105),
(‘Horses’, 106)
select * from #test
;
WITH CTE
AS
(
SELECT Animal
FROM #test
group by Animal
HAVING COUNT(1) > 1
)
SELECT #test.Animal +
CASE WHEN cast(CASE WHEN CTE.Animal IS NOT NULL THEN CAST(ROW_NUMBER() OVER (PARTITION BY #test.Animal ORDER BY (SELECT 0))AS VARCHAR) ELSE ” END as varchar) ” THEN
‘_’ + cast(CASE WHEN CTE.Animal IS NOT NULL THEN CAST(ROW_NUMBER() OVER (PARTITION BY #test.Animal ORDER BY (SELECT 0))AS VARCHAR) ELSE ” END as varchar) ELSE ” END
,#test.Outlet_No
FROM #test LEFT OUTER JOIN CTE
ON CTE.Animal = #test.Animal
The above query is the answer to Bhagya’s question..
Hi all,
I want to partition to two group (0-99).
Below is the table structure :
CREATE TABLE t
([no] int IDENTITY(1,1) PRIMARY KEY,[STEPID] decimal(8,1));
INSERT INTO t
([STEPID])
VALUES
–set 1
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),
(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(99),
–set 2
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),
(13.3),(16),(17),(18),(19),(20),(25),(99);
Pls advice. Thanks.
Very simple and effective talked, thank you
how to specify a value from which the ranking begins, like I want to start ranking from 1000 and 1001,1002,1003 .. so on.
adding 1000 to the rank will help.