Search code examples
sqloracletop-nsql-limit

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

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);
Solution

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