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

  • how to use date function in sql server…m new in sql server…….. plz help me

    Reply
    • Have a look at SQL Server helo file BOL and search for functions like DAY, MONTH, YEAR, DATEADD,DATEDIFF, etc. Also make sure to read the examples posted at the end of the following blog

      Reply
  • Hi Pinal, thanks for the article but it’s confusing and the results are not explained well, it’s taken from the 70-433 book examples and in my idea it’s not the best example ever.

    Recently I came across this article and it’s the greatest in explaining the aggregates SO FAR!!

    Reply
  • Thank you pinal. It helped me in a nice way.

    Reply
  • Prajakta Chaudhari
    April 14, 2012 5:22 pm

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

    Reply
  • Hi Prajakta,

    Can you please describe what exactly you are trying to achieve?

    Reply
  • hi sir,

    if i want to display the name of persons from a table who booked to travel in same day

    Reply
  • Hi Sir,

    From long time i want to know that in which scenarios we use rank() and dense_rank functions.

    Reply
  • saeedtech13
    May 3, 2012 6:03 pm

    tnx

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

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

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

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

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

    Reply
  • Dear pinaldave ,

    I have 5 MNC offer.ur articles helped me a lot. thanks for the great work :).

    Reply
  • Thanks you, this articles helped me a lot.

    Reply
  • Great example

    Reply
  • u r a genius man….. :)

    Reply
  • Hi Pinal,

    Have one question – When ROW_NUMBER(), RANK() & DENSE_RANK() will produce the same result?

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

      Reply
  • I am not clear with the NTILE function , can you explain it a bit more?

    Reply
  • very helpful article :-)

    Reply

Leave a Reply

Menu