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

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.

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.

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.

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.

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)

Joes 2 Pros, Ranking Functions, SQL Scripts, SQL Server
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

  • Rajesh Kumar Rolen
    August 12, 2011 5:37 pm

    SELECT * FROM
    (SELECT score, DENSE_RANK() OVER (ORDER BY Score DESC) as ScoreRating
    FROM #temp) AS ContestantFinal
    WHERE ScoreRating < 5

    so correct answer is :DENSE_RANK() OVER (ORDER BY Score DESC) as ScoreRating

    and another thing is that you will have to use "ScoreRating < 5" instead of "ScoreRating = 5"

    I am From INDIA

    Reply
  • Of the options provided, I would think you are looking for #4 to be the answer

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

    because your article states “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.”

    However running that query will only return those with scores equal to the 5th highest score. You’d acutally want something like this to return just the 5 highest scores…

    Select * FROM
    (SELECT distinct Score, DENSE_Rank() Over( Order by Score Desc) as ScoreRating
    From Contestants) as ContestantFinal
    where ScoreRating <= 5

    from the USA

    Reply
  • Shirish Kulkarni
    August 12, 2011 5:54 pm

    Option 4 is correct

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

    County : India

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

    Reply
  • Hi Pinal,

    Challenge:
    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

    Correct Answer:
    The correct answer will use #4:
    SELECT * FROM
    (SELECT *, DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
    FROM [Contestants]) AS ContestantFinal
    WHERE ScoreRating = 5

    Explanation:
    Choice #1 uses the aggregate SUM(), which will sum all the values. We don’t want the sum of the scores, so this choice is incorrect.

    Choice #2 uses Rank(). It will provide the 5 highest scores, but we are looking for the 5 highest distinct scores, so this choice is also incorect.

    Choice #3 uses the aggregate COUNT(), which will return the number 5. We don’t want this solution, so this choice is incorrect.

    Choice #4 uses DENSE_RANK(). It will provide the 5 highest distinct scores, which is what we are trying to retrieve. This is the correct choice to complete the SQL statement above.

    Country:
    United States

    Thanks for the knowledge!

    Regards,

    Bill Pepping

    Reply
  • The correct answer is #4

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

    The DENSE_RANK() OVER() clause will select distinct scores. RANK() OVER() will retrieve duplicates in the result set.

    SELECT ‘USA’ AS [COUNTRY];

    Reply
  • The Correct Option is

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

    Country-India

    Reply
  • Correct answer is No. 4
    DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
    as it would give the five highest distinct amounts

    (Sale, Nigeria)

    Reply
  • srinivas reddy
    August 12, 2011 7:29 pm

    Correct Answer is : option 4
    Country: USA

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

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

    Posted to quickly (speeding towards Friday evening)

    Gordon Kane
    Allen TX
    USA

    Reply
  • The answer is #4

    David
    USA

    Reply
  • The correct answer is #4

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

    Jitendra Gupta
    Copenhagen,Denmark

    Reply
  • the correct answer is number 4
    tarek abosalem
    Egypt
    Assiut

    Reply
  • Correct option is #4

    Country – India

    Reply
  • Answer: None of the above.

    Answer 4 is very close to the answer and to correct that we should add with that (WHERE ScoreRating <= 5)

    -Country: India

    Reply
  • Sayli Deshmukh (@sddesh)
    August 12, 2011 9:19 pm

    Answer 4, but with scorerating <=5 in where clause.
    Since the current query will list all details in the table with scorerating =5.

    Sddesh
    USA

    Reply
  • Dnyanesh Deshpande
    August 12, 2011 10:37 pm

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

    This is correct option.

    India

    -Dnyanesh

    Reply
  • vinay (@vinayprasadv)
    August 12, 2011 11:02 pm

    Answer is :

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

    Vinay
    Pune, India

    Reply
  • Option 4 will yield the desired results.

    Country: United States

    Reply

Leave a ReplyCancel reply

Exit mobile version