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.
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?
- SUM(*) OVER(ORDER BY Score DESC) as ScoreRating
- RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
- COUNT(*) OVER(ORDER BY Score DESC) as ScoreRating
- 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)
103 Comments. Leave new
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
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
Answer : 4
DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
India
Correct option is 4.
Shilpa
India
Option-4 is the Correct Answer
Thanks,
Narendra(India)
Correct answer is 4
4.DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
City: Baroda
Country: INDIA
Thanks
GurjitSingh
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
DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating is Correct one..
Thanks.
Mithil Mehta(India)
Correct answer is option 4
4.DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
Cheers,
Prasad Yangamuni
INDIA ( PUNE)
Correct option is 4.
DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
Pratik Raval
India
Correct Answer is Option 4
DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
Country:India
Thanks,
Fazal
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
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
Correct Answer – 4
DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
Deepali Bhende
Country – INDIA
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
Nice Example Sir.
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
Option 4 is Correct
4.DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
India
U r rite rajesh..we need to use distinct over there
Correct answer is 4.
Country : India
City : Mumbai
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
Somnath From India
Answer 4
4.DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
Country : India