A query that shows the cost within $2 of the most expensive film

Question

TITLE   GEN DURATION    PRODUCTION_YEAR MED PURCHASE_PRICE  PURCHASE_   MOVIE_ID

those are my columns

I am using oracle 9i

Thanks to anyone who can help

Answer 1

You can use the MAX function in sub-query in WHERE clause as follows:

SELECT * FROM MOVIE
 WHERE PURCHASE_PRICE >= (SELECT MAX(PURCHASE_PRICE) - 2 FROM MOVIE)

Answer 2

You know how to get the price of the most expensive movie. So just use that information to filter all the movies.

with exmv as 
  (select max(purchase_price) as mx_price 
   from movie)
select movie.*
from       movie 
cross join exmv
where movie.purchase_price >= exmv.mx_price - 2
;

This query puts the aggregate query in a subquery factoring clause which it references in the FROM clause. We use a CROSS JOIN because we know the subquery returns only one row, so we don't need any join criteria. The WHERE clause filters on the basis of price.

---

You say you are using Oracle 9i. Why such an old version? It's been out of Extended Support for a decade! Anyway, that's the version which introduced the WITH clause (and the ANSI-92 join syntax). It's in the documentation. Find out more

Answer 3

You can do this with analytic functions, that are available in Oracle 9i:

select *
from (
    select m.*, max(purchase_price) over() as max_purchase_price
    from movie m
) m
where purchase_price >= max_purchase_price - 2

Answer 4

You can use window functions:

select t.*
from (select t.*, max(PURCHASE_PRICE) over () as max_price
      from t
     ) t
where PURCHASE_PRICE >= max_price - 2;