SQL SERVER – Ranking Functions – RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) – Day 12 of 35

In this blog post we will discuss about Ranking Functions like RANK( ), DENSE_RANK( ), and ROW_NUMBER( ).

Ranking Functions (Part 1)

There are four ranking functions in SQL server. Today we will look at RANK( ), DENSE_RANK( ), and ROW_NUMBER( ).These functions all have the same basic behavior. Where they differ is in the handling of tie values. These three functions produce identical results, until a tying value in your data is present.

SQL SERVER - Ranking Functions - RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) - Day 12 of 35 j2p_12_1

Until the tie data is encountered, the results are identical (see Josh and Kevin’s score of 9.6. We will explain more on this later. Let’s look at RANK( ) using a Grant table example sorted by largest to smallest grant amount (descending sort order).

SQL SERVER - Ranking Functions - RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) - Day 12 of 35 j2p_12_2

A smart way to begin your ranking functions is to first write a SELECT statement with an ORDER BY clause. Every ranking function retrieves your sort order from the ORDER BY clause. In this case, the item is grant amount arranged in descending order. Add RANK( ) to the SELECT list. Remember that all ranking functions need an OVER( ) clause. If you then SQL Server reminds you as you can see from the error message below.

SQL SERVER - Ranking Functions - RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) - Day 12 of 35 j2p_12_3

Solarwinds

All ranking functions need an OVER( ) clause and SQL Server provides another syntax clue for the ORDER BY clause. Ranking functions need the ORDER BY information to appear as an argument of the OVER( ) clause Move the ORDER BY clause inside the parentheses of OVER( ) clause.

SQL SERVER - Ranking Functions - RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) - Day 12 of 35 j2p_12_4

Now we can look at how RANK() works with ties. The Grant table ranked over the Amount column, showing 2 pairs of tied amounts. There is a 2 way tie for 4th place so both the grants of $21,000 get a rank of 4. After that there is no 5th place grant. There is a 6th place grant so after ties you get a gap to match the numbered order. Observe that no ORDER BY clause follows the FROM clause, since we moved it in into the OVER( ) clause. Examine what the RANK( ) function has done here. We see straightforward rankings of 1, 2, and 3 for the first three unique amount values. Notice the tie for fourth place. Then there’s another tie for the next place, which is ranked as 6. After that we get to 8th place as seen in the figure below.

SQL SERVER - Ranking Functions - RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) - Day 12 of 35 j2p_12_5

How about Dense_Rank? After the 2 way tie for 4th place the next value will get 5th place. There will be no gaps after the tie with the DENSE_RANK(). DENSE_RANK( ) is very useful for finding the five highest distinct amounts, because it finds each amount and has no gap in its numbering sequence.

SQL SERVER - Ranking Functions - RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) - Day 12 of 35 j2p_12_6

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLQueriesChapter7.0Setup.sql script from Volume 2.

Question 12

The figure below shows the scores of 6 contest winners. Tom is the highest
and Eric made 6th place. There were 6 people but only 5 distinct scores.

5 Highest Scores 5 Highest Distinct Scores

9.9 9.9

9.8 9.8

9.7 9.7

9.6 9.6

9.6 9.2

You are writing a query to list the 5 highest distinct scores. The Ranked field should be called ScoreRating. You have written the following code.

SELECT * FROM
(SELECT  *  More code here.
FROM [Contestants])  AS ContestantFinal
WHERE ScoreRating <= 5

What code will achieve this goal?

  1. SUM(*) OVER(ORDER BY Score DESC) as ScoreRating
  2. RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
  3. COUNT(*) OVER(ORDER BY Score DESC) as ScoreRating
  4. DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 2.
The contest is open till next blog post shows up at which is next day GTM+2.5.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Advanced Aggregates with the Over Clause – Day 11 of 35
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Ranking Functions – Advanced NTILE in Detail – Day 13 of 35

Related Posts

103 Comments. Leave new

  • DiveSh Singhvi
    August 12, 2011 11:20 pm

    Answer – 4 DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

    Divesh
    INDIA

    Reply
  • DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

    Can be used to five highest distinct amounts with scorerating <=5 in where clause

    USA

    Reply
  • Answer : 4

    1 -> Because * can not be used in Sum Function
    2 -> Because Misses the rank numbers
    3 -> count(*) will count for all rows..this result we are not expecting

    Country -> USA

    Reply
  • Vivek Srivastava
    August 13, 2011 12:15 am

    Option 4 is correct answer.

    DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

    SELECT * FROM
    (SELECT * , DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating.
    FROM [Contestants]) AS ContestantFinal
    WHERE ScoreRating <= 5

    Thanks
    Vivek Srivastava
    Country:- INDIA

    Reply
  • SELECT distinct * FROM
    (SELECT *, Dense_Rank() OVER(ORDER BY Score DESC) as ScoreRating FROM [Rank]) AS ContestantFinal
    WHERE ScoreRating <= 5
    order by ScoreRating asc

    This code will precisely provide the distinct score and score ranking

    Rishi
    USA

    Reply
  • dilipkumarjena
    August 13, 2011 1:47 am

    Hi Pinal Sir,

    The Correct option for the above question is Option 4.
    4. DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

    Explanation:

    We need to write a query to list the 5 highest distinct scores. The Ranked field should be called ScoreRating.

    SELECT * FROM (SELECT * DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating FROM [Contestants]) AS ContestantFinal WHERE ScoreRating <= 5

    The below Query just Gives that.

    As, Explained by you DENSE_RANK( ) is very useful for finding the five highest distinct amounts, because it finds each amount and has no gap in its numbering sequence. Now what if we have similar value it repeats and then gives the next value no matter how many times it repeated the same Rank.

    By the result and query the Option by far stands the correct Option.

    Why Other Options are wrong:

    Option 1 )SUM(*) OVER(ORDER BY Score DESC) as ScoreRating

    Here we need to list the 5 highest distinct scores not the sum so ruled out.

    Option 2)RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

    Here its a good Question I read in one of the comment it is written that this can be a good contender for correct option but how because here 9.6 is repeating if there are other option or number which repeats other than this how come Rank will return 5 highest distinct scores because on the first table it goes from 4 to 6 then 6 never less then 5 so we will have only 5 distinct values and comming to tell that Pinal sir has done a typing blunder, wrong how if we write ScoreRating = 5 the query will return only the records where ScoreRating equates to 5 it may have 1 or no record.

    Here the table given by you has repeated 9.6 and we dont know how many times but will continue from where it left so last record we need to complete 5 distinct values is 9.2. Now if we write only ScoreRating <= 5 how we will get 5 th unique record because 5 is never less than 5. so it should be ScoreRating <= 5.

    Hence this is completly wrong.

    Option 3) COUNT(*) OVER(ORDER BY Score DESC) as ScoreRating

    Here we require 5 highest distinct scores not count so ruled out.

    So the correct option is 4

    Dilip Kumar Jena

    Country : INDIA

    Reply
  • dilipkumarjena
    August 13, 2011 1:49 am

    Hi Pinal Sir,

    Correction :

    The Correct option for the above question is Option 4.
    4. DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

    Explanation:

    We need to write a query to list the 5 highest distinct scores. The Ranked field should be called ScoreRating.

    SELECT * FROM (SELECT * DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating FROM [Contestants]) AS ContestantFinal WHERE ScoreRating <= 5

    The below Query just Gives that.

    As, Explained by you DENSE_RANK( ) is very useful for finding the five highest distinct amounts, because it finds each amount and has no gap in its numbering sequence. Now what if we have similar value it repeats and then gives the next value no matter how many times it repeated the same Rank.

    By the result and query the Option by far stands the correct Option.

    Why Other Options are wrong:

    Option 1 )SUM(*) OVER(ORDER BY Score DESC) as ScoreRating

    Here we need to list the 5 highest distinct scores not the sum so ruled out.

    Option 2)RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

    Here its a good Question I read in one of the comment it is written that this can be a good contender for correct option but how because here 9.6 is repeating if there are other option or number which repeats other than this how come Rank will return 5 highest distinct scores because on the first table it goes from 4 to 6 then 6 never less then 5 so we will have only 5 distinct values and comming to tell that Pinal sir has done a typing blunder, wrong how if we write ScoreRating = 5 the query will return only the records where ScoreRating equates to 5 it may have 1 or no record.

    Here the table given by you has repeated 9.6 and we dont know how many times but will continue from where it left so last record we need to complete 5 distinct values is 9.2. Now if we write only ScoreRating < 5 how we will get 5 th unique record because 5 is never less than 5. so it should be ScoreRating <= 5.

    Hence this is completly wrong.

    Option 3) COUNT(*) OVER(ORDER BY Score DESC) as ScoreRating

    Here we require 5 highest distinct scores not count so ruled out.

    So the correct option is 4

    Dilip Kumar Jena

    Country : INDIA

    Reply
  • 4.

    Dense_Rank returns distinct values

    Dan
    New Jersey USA

    Reply
  • hi friend ‘s i have a question to all who belong to database pls give me ans if u know friend’s pls….. question is:- how we can retrieve four highest sallry from a table pls tell me query

    Reply
  • The correct answer is 4 DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating because it returns distinct values in case of a tie

    Eric
    USA

    Reply
  • 4)

    DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

    Location : London UK

    Reply
  • Q 12) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Ranking Functions – RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) – Day 12 of 35

    A.) Because SUM and COUNT both expect the PARTITION BY argument in the OVER ( ) clause the code in (1) and (3) will both return ‘Incorrect syntax near the word ‘order ’’. Since RANK ( ) will skip numbers after a tie the records returned may not be distinct values making (2) incorrect too. Because DENSE_RANK assigns the rank based on distinct values (4) is the correct answer.

    Winner from USA: Deb

    Winner from India: Dayanand Singh

    I thank you all for participating here. The permanent record of this update is posted on facebook page.

    Reply
  • Rajesh Mohanrangan
    August 16, 2011 4:32 pm

    Ans-4:

    DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

    Regards
    Rajesh.M

    Reply
  • Rajesh Mohanrangan
    August 16, 2011 4:32 pm

    Ans-4:

    DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

    Regards
    Rajesh.M
    From India

    Reply
  • Nikhil Mahajan
    August 17, 2011 8:10 am

    Correct answer is No. 4

    DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
    because difference between rank() and dense_rank is dense_rank wont skip the number id there is tie between two numbers..

    so correct answer is 4

    India

    Reply
  • Answer is #4:
    DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

    Thanks,
    Wayne (USA)

    Reply
  • Bhavisha Patel- USA
    August 22, 2011 1:04 am

    Answer: DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

    As the question here we are trying to get distinct highest 5 scores here with using the DENSE_RANK( ) we can get this ranking because we are getting all number as a rank without skipping any number with the tied value.
    So this is the best answer for get all distinct highest five score.

    Reply
  • Thanks for the info. Really helped me a lot.

    Reply
  • Muhammad Waseem Baccus
    February 26, 2018 7:17 pm

    4. DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
    RSA
    MWB

    Reply

Leave a Reply

Menu