How to return top 3 rows if last row is tied

Question

Suppose my table contains data like that

  ID    MovieName                      Rating    
  --------------------------------------------
  1     The Shawshank Redemption       9.20
  2     The Godfather: Part II         9.00
  3     12 Angry Men                   8.90
  4     Pulp Fiction                   8.90
  5     The Good, the Bad and the Ugly 8.80

I want to select top 3 movies according to highest rating which contains both '12 Angry Men' and 'Pulp Fiction' movies.So query should return 4 rows instead of 3.

Tag properly. It's either SQL Server or Oracle, can't be both. What have you tried so far?????? Learn how to ask question. https://stackoverflow.com/help/how-to-ask — Eric, Dec 05 '17 at 19:05

score 4 · Accepted Answer · answered Dec 06 '17 at 02:56

4

Just use TOP WITH TIES

SELECT TOP 3 WITH TIES ID, MovieName, Rating
FROM MyMoviesDB
ORDER BY Rating DESC

Only thing is you have to use ORDER BY

answered Dec 06 '17 at 02:56

Ruslan Tolkachev

634
4
9

Vash · Answer 2 · 2017-12-05T19:25:31.557

0

Try:

SELECT A.ID, A.MOVIENAME, A.RATING
FROM
(SELECT ID, MOVIENAME, RATING, DENSE_RANK() OVER (ORDER BY RATING DESC) AS R
FROM YOUR_TABLE) A
WHERE A.R <= 3
ORDER BY A.RATING DESC;

Dense Rank will repeat for same ratings. So if one of the top 3 ranks have more than one occurrences, you will get more than 3 entries in the output.

edited Dec 05 '17 at 19:25

answered Dec 05 '17 at 19:09

Vash

1,767
2
12
19

May also want to consider `DENSE_RANK` for this application. It will depend on if skipping places or not is desired. e.g. what happens if 1st or 2nd place has a tie with `RANK` you could drop off 2nd or 3rd place but with `DENSE_RANK` they would be represented. https://stackoverflow.com/a/11183610/5510627 – Matt Dec 05 '17 at 19:21
Too complex solution. Use `WITH TIES` option: https://learn.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql – Backs Dec 06 '17 at 03:49
Why would someone downvote this answer despite being correct and simple?! DENSE_RANK is one of the best approaches for this kind of problems! – Vash Dec 06 '17 at 04:02
Suppose there are five movies in the table and the set of scores is (10, 9, 9, 8, 8). The `WITH TIES` solution will return the top three, but the `DENSE_RANK` approach will return all five. Since the OP asked for the top three movies, with a fourth (and potentially more) included only if it would be tied for third, I think `WITH TIES` is the more correct solution. – Joe Farrell Dec 06 '17 at 15:24
OP wants top 3 movies **based on the ratings**. Returning strictly 3 entries is not a restriction if one **or more** of the top 3 ratings is/are repeating. He provided an illustration of 3rd to explain this. One is less likely to interpret(unlike you did) that it specifically meant for the 3rd rank. The best solution is to always generalize it for all of top 3 ratings. And BTW, the output in your example should be (10,9,9,8,8) according to my interpretation. I hope this clears the confusion. I still think the downvotes are unfair based on how OP has explained and my interpretation of it. – Vash Dec 06 '17 at 18:32
I downvoited this answer becase it's not optimal and there is much more suitable solution (`WITH TIES`). For me it's like for question `How to add A to B?` give solution `int sum(int a, int b){ return (!a||!b ? a|b : sum((a&b)<<1,a^b));}`. Yes, it works, you can explain it, but it's not optimal for this. https://ideone.com/9Bd7TQ – Backs Dec 08 '17 at 05:53

score 0 · Answer 3 · answered Dec 05 '17 at 20:31

You would want to select the rows in the moviesdb table where the Rating is in the DISTINCT Top 3 Ratings Ordered BY Ratings in Desc Order:

SELECT * 
  FROM moviesdb
 WHERE Rating IN (SELECT DISTINCT TOP 3 Rating 
                    FROM moviesdb 
                   ORDER BY Rating Desc)

score -1 · Answer 4 · answered Dec 06 '17 at 03:23

-1

Select * from Table_Name
ORDER BY Rating
LIMIT 4

answered Dec 06 '17 at 03:23

Cookie Monster

636
1
12
29

The `SELECT` statement doesn't have a `LIMIT` clause in SQL Server, and even if it did, you're assuming that there is exactly one tie among the top three unique scores in the data. If there are more, or none, then the expected size of the result set will be different. – Joe Farrell Dec 06 '17 at 15:19

How to return top 3 rows if last row is tied

4 Answers4