What was the cost for the most expensive movie(s) in the collection?

Question

Hey guys I know the code to show the most expensive movie but what's the one that will show the most expensive and ones right below it. I think that's the question. This is the code I got for one movie.

SELECT *
   FROM movie
   WHERE purchase_price = 
    (SELECT MAX(purchase_price) FROM movie);

What's the expected result if there are two movies with the same, second most expensive price? — jarlh, Dec 01 '20 at 08:48

score 1 · Accepted Answer · answered Dec 01 '20 at 08:57

Well since your description is a little ambiguous, to find your prefer solution, you will have to try several of them. For example, you can try by using an ORDER BY Condition. Using this condition, you will retrieve all the movies starting with the most expensive one at the top.

SELECT
    *
FROM
    movie
ORDER BY
    purchase_price DESC;
FETCH FIRST 2 ROWS ONLY

But yet again, there are other solutions you can try as well. You can RANK them by price in a subquery and then fetch all the answers. Another example would be to use between max and min ( or any other value ). You can reach even some more technical and harder to implement solutions.

score 0 · Answer 2 · answered Dec 01 '20 at 08:51

Rank them by price (in a subquery), and fetch the first two. Something like this:

select *
from (select m.*,
             rank() over (order by m.purchase_price desc) rnk
      from movie m
     )
where rnk <= 2

Depending on data you have, you might also consider using ROW_NUMBER or DENSE_RANK analytic functions.

score 0 · Answer 3 · answered Dec 01 '20 at 08:53

If you strictly want the two most expensive movies, you could order the query and use a fetch first clause:

SELECT *
FROM  movie
ORDER BY purchase_price DESC
FETCH FIRST 2 ROWS ONLY

If multiple movies can have the same price, and you want to find all the movies with two most expensive prices, you could use the dense_rank window function:

SELECT *
FROM   (SELECT *, DENSE_RANK() OVER (ORDER BY purchase_price DESC) AS rk
        FROM   movie) m
WHERE  rk <= 2

score 0 · Answer 4 · answered Dec 01 '20 at 09:19

I would rather use the FETCH FIRST 2 ROWS WITH TIES option which will give you the first two most expensive movies and also takes care of multiple movies with the same purchase price

SELECT *
 FROM  movie
ORDER BY purchase_price DESC
FETCH FIRST 2 ROWS ONLY TIES;

What was the cost for the most expensive movie(s) in the collection?

4 Answers4

Linked