SQL SERVER – 2005 – Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

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:
SQL SERVER - 2005 - Sample Example of RANKING Functions - ROW_NUMBER, RANK, DENSE_RANK, NTILE ranking

Most of the content of this article is taken from BOL.

Reference : Pinal Dave (https://blog.sqlauthority.com), ROW_NUMBER, RANK, DENSE_RANK, NTILE

, ,
Previous Post
SQL SERVER – 2005 – Connection Property of SQL Server Management Studio SSMS
Next Post
SQL SERVER – UDF vs. Stored Procedures and Having vs. WHERE

Related Posts

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.

    Reply
  • how to write a function for generate uniqe employee id
    ex,, emp-kk-0001,emp-kk-0002,,Function?

    Reply
    • 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)
      )

      Reply
  • 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

    Reply
  • Ghulam Mustafa
    March 11, 2013 11:42 pm

    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

    Reply
  • Antony Prasanth
    June 12, 2013 11:26 am

    Very good explanation ,Thanks.

    Reply
  • I need the Email_Id’s from the Data column to be sorted in asc order.

    Reply
  • 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.

    Reply
  • 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;

    Reply
  • 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

    Reply
    • Vishal Motwani
      June 13, 2016 8:54 pm

      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

      Reply
  • 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.

    Reply
  • Honza Zednicek
    October 13, 2015 8:25 pm

    Very simple and effective talked, thank you

    Reply
  • how to specify a value from which the ranking begins, like I want to start ranking from 1000 and 1001,1002,1003 .. so on.

    Reply
  • adding 1000 to the rank will help.

    Reply

Leave a Reply

Menu