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:

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

Reference : Pinal Dave (http://blog.SQLAuthority.com), ROW_NUMBER, RANK, DENSE_RANK, NTILE

About these ads

64 thoughts on “SQL SERVER – 2005 – Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

  1. Hi Pinal,

    Can we use ROW_NUMBER() function in views ?
    If not then what is the alternative method to give the row numbers in view

    Thanks
    -Sudhir

    • Very clearly described the definitions and example of all the 4. I was searching exactly this kind of concise & clear stuff.

      Dave, Thanks.

      -Manoj

  2. Hi Pinal,

    I have MS SQL 2000 on production server and as you know these functions are not available on same. My developer has used these functions extensively in application and insisting me to upgrade MS SQL 2000 to MS SQL 2005. This is not correct time for me to upgrade my system so can you please tell me the way out. Is it possible to use these functions in MS SQL 2000 by installing some patches or sp or by installing only t-sql of MS SQL 2005.

    Thanks

    Vikram

  3. yes we can use try this……………..
    CREATE VIEW testview AS SELECT ROW_NUMBER() OVER (ORDER BY )-1 as autoIndex,

    * FROM

  4. hello,

    Please clafiry this doubt In MYSQL query can use like this
    select * FROM `table` WHERE MATCH (`email`) AGAINST (‘+firstname.lastname@hotmail.com’);
    where incase of MSSQL what quey can use to select record from database exact records ..for example in textbox i’m entering chennai,software,IT. i want all the records related to this string .. please reply

  5. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  6. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 8 Journey to SQL Authority with Pinal Dave

  7. Hi Pinal,

    I try to determine the newest Price from multiple Items in a PriceTable. In Oracle, I can do this with ranking functions:

    SELECT ItemId, MAX(Price) KEEP(DENSE_RANK FIRST ORDER BY FromDate DESC) AS Price
    FROM PriceTable
    GROUP BY ItemId

    Is it possible to do the same with ranking functions in MSSQL?

    Thanks
    Martin

  8. Hi Pinal,
    I’m trying to convert these two rows to access sql.

    dense_rank() over(partition by field1 order by field2) as name1,

    row_number() over(partition by fld1, (dense_rank() over(partition by fldnm1 order by fldnm2)) order by fld2) as name2

    can you help me?
    thanks in advance

  9. hi sir,
    this is hanu, i have ony doubt that is

    how to store dense_rank() function results in sqlserver database.

    give me solution with example, thank u sir

    ex:

    sid same marks rank
    1 hanu 68 3
    2 bhanu 78 2
    3 ramu 80 1

  10. @Yogitha bali g.s

    Question : Display the different types of credit caeds used for purchasing products?

    Answer :
    VISA
    MASTER
    AMERICAN EXPRESS
    Blah Blah….

    How I am I suppose to know this information.

    Please post your complete question, with complete details.

    ~ IM.

  11. hi ,
    Can u let me know how 2 ignore ranking if the column value is null.for e.g

    colX Rank
    52 7
    108 1
    80 2
    80 2
    null 8

    in the above case i want the ranking for 5th row to be null instead of 8.Need ur guidance

    • Hi Balaji,

      Yes you can ignore Null using the CASE condition before the RANK() function. I below pasted the code for your reference.
      SELECT * FROM dbo.EmpDetails
      I leaved one column(EmpSal) as null in this table.

      SELECT emp.EMPFName,(CASE WHEN emp.EMPSal is not null Then ROW_NUMBER()OVER(ORDER BY emp.EmpSal)ELSE NULL END) AS ‘Row Number’, RANK() OVER(ORDER BY emp.EmpSal)
      as ‘RANK’
      FROM dbo.EmpDetails emp

  12. Hi Pinal,
    I want to get some amount of rows from a table, e.g. 10 rows, from 15th row in the table ordered by some column.
    how to use row_numberIO or any other functions

    Another Question:
    Can Identity Column is attached to select list in any select Query susch as

    select Identity(1,1) as row_number,col1,col2 from table1

  13. Hello – please help – I want to rank records in the table and store this rank in the same table for further processing – what is the simplest way to do it? I’m working in SQL 2005……

  14. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 18 of 31 Journey to SQLAuthority

  15. Hi,
    I have strange requirement .

    I need to show ranks for all the rows except few in between.
    I tried that in using where id’s not in (‘1′,’2′)

    In this case I’m getting only the rows where id’s not in (1,2)

    Is there any way to get those rows as well in the same query ?

    Or Do I need to Follow a different approach?

  16. Query

    I have a list of runners and their race times and ages. Write a
    Query to display the runners sorted by finish time with a column showing
    their finish position (ranking) within their age group.

    The age groups are
    0 – 15 years
    16 – 29 years
    30+ years

    Name Time Age
    ——————–
    Steve 12 33
    Tim 34 28
    Mark 22 37
    Tom 21 30
    Cliff 13 33
    Vini 17 28
    Matt 10 28
    Ben 9 29
    Brandon 15 14

    Output should be like below

    Name Time Age Ranking
    ===== ==== === =======
    Ben 9 29 1
    Matt 10 28 2
    Steve 12 33 1
    Cliff 13 33 2
    Brandon 15 14 1
    Vini 17 28 3
    Tom 21 30 3
    Mark 22 37 4
    Tim 34 28 4

    • select Ranking,BloggerName from
      (select BloggerName,Row_Number() OVER (Partition by Topic Order by Total) ‘Ranking’
      FROM BlogCount where BloggerName=’Ritesh’) t where Ranking not in(2)

  17. Hi Sir,
    Can we use Row_Number in multiple join queries?
    Actually i am trying to get that but getting error

  18. DECLARE @S varchar(max),
    @Split char(1),
    @X xml

    SELECT @S = ‘1,2,3’,
    @Split = ‘,’

    SELECT @X = CONVERT(xml,” + REPLACE(@S,@Split,”) + ”)
    insert tablename
    SELECT [Value] = T.c.value(‘.’,’varchar(20)’)
    FROM @X.nodes(‘/root/s’) T(c)
    my current answer is
    a
    1
    2
    3

    I expect this answer ,what can i do for this
    a b c
    1 2 3

  19. I am executing same stored procedure on two servers. One execution is faster whereas other is slow compared to other. what are the possible reasons for this?

  20. I am having values in a column as 1,1,1,2,2,2,3,3,3. I want to write a query which results me like 1,2,3,1,2,3,1,2,3. How can we do this?

  21. Hi Pinal,

    The blog is very interesting ..And I am not able to understand about NTILE() function..Can you please one more example with explanation..

  22. Hi Pinal,

    I have a string @value = ~46|tblAccount|OwnerID|2012.07.20|7|5~279|tblAccount|Name|2012.07.20|Lyrics|Future|~279|tblAccount|OwnerID|2012.07.20|4|5

    Now i have to split the result into a new columns and new rows with the respective delimiters , We need the result as :

    1) where ever ‘~’ is present as a new record
    2) wherever ‘|’ is present into a new column

    the above result should look like

    Column1 Column2 Column3 Column4 Column5 Column6

    46 tblAccount OwnerID 2012.07.19 7 5
    279 tblAccount Name 2012.07.19 Lyrics Future
    279 tblAccount OwnerID 2012.07.19 4 5

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

      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

  23. 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.

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

  24. 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

  25. Pingback: SQL SERVER – Weekly Series – Memory Lane – #050 | Journey to SQL Authority with Pinal Dave

  26. 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.

  27. 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;

  28. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s