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

Answer simple quiz at the end of the blog post and -
Every day one winner from India will get Joes 2 Pros Volume 2.
Every day one winner from United States will get Joes 2 Pros Volume 2.

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.

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.
Winner from India will get Joes 2 Pros Volume 2.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

106 thoughts on “SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Ranking Functions – RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) – Day 12 of 35

  1. Correct answer is No. 4
    DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
    as we want to get the five highest distinct amounts.

    Rene Castro
    El Salvador

  2. This time I guess you have a typo. For the five highest distinct scores none of your choices give the answer mostly because of typo in main body of the question. 2) and 4) are syntactically correct

    4) is the one closest to the solution as it presents only the distinct values of scores

    The question should have
    “WHERE ScoreRating <= 5" instead of "WHERE ScoreRating = 5"

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

    Leo Pius
    USA

  3. Forgot to add in my prev. reply – I’m from USA

    The correct option is #4

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

    DENSE_RANK provides distinct scores.

  4. Correct answer is option #4

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

    becuase as explained in the article above 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.

    Thanks.

    Country – India

  5. Actually, when I think a bit more, the question asked is quite tricky.

    If we want to list just the five distinct top scores, we can do:

    ;with Scores as (select Score, dense_rank() over (partition by Score DESC) as [ScoreRank] from ContestantFinal)

    select Distinct Score from Scores where ScoreRank <=5

    ———————————————————————-
    With the query #4

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

    we will get all people who were on the 5th place.

    I am from USA

  6. What code will achieve this goal?
    Main query is itself wrong
    as where condition looking for ScoreRating=5
    This wont give Correct result
    Does not give any distinct values.

    If In where condition ScoreRating<6 then we can use option 4 in sub-query
    Answer is 4
    4. DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
    Gives but does not give the distinct ratings.

    wrong:
    1. SUM(*) OVER(ORDER BY Score DESC) as ScoreRating
    Because * can not be used in Sum Function
    2. RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
    Because Misses the rank numbers
    3. COUNT(*) OVER(ORDER BY Score DESC) as ScoreRating
    count(*) will count for all rows..this result we are not expecting and

  7. Option 4 is correct.

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

    As we need to return the scorerating 5, it is only possible if we use DENSE_RANK here.

    INDIA

  8. Answer 4:

    DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
    as we want to get the five highest distinct amounts.

    India

  9. a query to list the 5 highest distinct scores write the following code.

    SELECT * FROM

    (SELECT * , DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

    FROM [Contestants]) AS ContestantFinal

    WHERE ScoreRating <=5

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

    Krishnakant Joshi
    India

  10. Ans is 4:

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

    Partha
    India

  11. The correct answer is option 4 that is
    DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

    So your code will now look like this

    SELECT * FROM
    (
    SELECT * , DENSE_RANK( )
    OVER(ORDER BY Score DESC
    ) as ScoreRating

    FROM [Contestants]) AS ContestantFinal
    WHERE ScoreRating = 5

    This will give you the desired result

    Mahmad Khoja
    INDIA
    AHMEDABAD

  12. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  13. Correct answer is 4

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

    City: Baroda
    Country: INDIA

    Thanks
    GurjitSingh

  14. The correct answer : Option 4

    DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
    The actual Query is like this
    SELECT * FROM
    (SELECT * , DENSE_RANK( ) OVER(ORDER BY Score DESC) ‘ScoreRating’
    FROM [Contestants]) AS ContestantFinal WHERE ScoreRating = 5

    – GVPrabu || Bangalore || India

  15. Hi Sir,

    None of the options provided are answer for the question.

    1st and 3rd Option is syntactically incorrect which will throw an error.

    2nd option is syntactically correct but will provide null result
    as Rank() will provide 5 values of rank 1,2,3,4,4 and there is no rank which is equal to 5

    SELECT * FROM
    (SELECT *, RANK() OVER(ORDER BY Score DESC) as ScoreRating
    FROM PlayerScore) AS ContestantFinal
    WHERE ScoreRating = 5

    4th Option is syntactically correct

    SELECT * FROM
    (SELECT *, DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
    FROM PlayerScore) AS ContestantFinal
    WHERE ScoreRating = 5

    This option will provide only the 5th ranked value that is

    Player : Eric
    Score : 9.20
    ScoreRating : 5

    So all the options does not satisfy the question which says to list 5 Highest distinct scores and the ranks.

    The correct query is :

    SELECT * FROM
    (SELECT Distinct Score, DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
    FROM PlayerScore) AS ContestantFinal
    WHERE ScoreRating <= 5

    this will provide the required result for the question with the result

    Score ScoreRating
    ——- ————–
    9.9 1
    9.8 2
    9.7 3
    9.6 4
    9.2 5

    Nice question…:)

    Thanks

    P.Anish Shenoy,
    INDIA

  16. Answer is 4

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

    DENSE_RANK() function create same rank number for same value.

    Country : India
    City: Ahmedabad

  17. Correct Answer – 4

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

    example code run –

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

    Shekhar Gurav.
    Country : INDIA

  18. Hi Pinal
    Option 4 is correct. But need to some changes in the query

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

    Rajesh Garg
    India

  19. Option No:- 4

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

    You will get all the people who were on the 5th place.

    Somnath Desai

  20. 1. SUM(*) will give a syntax error. Hence Eliminated.
    2. Rank will skip the next numbers after occurance of multiple values at same ranking position. Hence Eliminated.
    3. Count(*) will count all the rows. Hence Eliminated.
    4. DENSE_RANK() will give the ranking position without skipping any number.

    Hence 4 is the correct option.

    Ishan Shah
    Gandhinagar,
    India

  21. 4. DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
    as we want to get the five highest distinct amounts.

    Ghanshyam
    bangalore

  22. Option 4 is the correct ans.

    Syntax is as follow :

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

    Pritesh Mehta.
    India.

  23. Option : 4

    DENSE_RANK OVER (ORDER BY Score DESC) as ScoreRating

    Above code only matches to get top 5 Score Rating.

    Chennai, India

  24. The correct answer is option 4
    DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

    but the query needs to be changed. it should be <=5 , NOT =5 as it will return record with ScoreRating 5 only

    Sumit
    India

  25. As per requirement none of the option provide correct result as last option only provide the fifth highest distinct score.
    For getting all five distinct highest scores where clause must be ‘<=' instead of '=' .
    After saying so, i will say DENSE_RANK() function provide a unique rank no to each of the unique score whereas RANK() function provide a unique rank to unique score but skips the one rank no.. if two scores were same. SUM() and COUNT() function provides sum and count of total no of scores. So, Option 1,2,3 are not at all nearer to the solution .
    Hence, Only option nearer to the solution is option 4

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

    Country – INDIA (Gujarat)

  26. Option : 4 is correct.

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

    As per requirement this query will return 5 distinct values.

    Thanks,
    Rajasekar.K
    Bangalore – India.

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

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

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

  30. 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];

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

    Posted to quickly (speeding towards Friday evening)

    Gordon Kane
    Allen TX
    USA

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

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

    USA

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

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

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

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

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

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

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

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

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

  42. Pingback: SQL SERVER – SQL Query Techniques For Microsoft SQL Server 2008 – Book Available for SQL Server Certification Journey to SQLAuthority

  43. Pingback: SQL SERVER – Identity Fields – Contest Win Joes 2 Pros Combo (USD 198) – Day 2 of 5 « SQL Server Journey with SQL Authority

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

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