CODEWITHSUNDEEP
sqloracle-database
Jen Talbot
Jen Talbot

Reputation: 11

How to use an aggregate function in SQL WITH JOINS?

The following is a query that I built to find the cheapest price for a particular movie along with the distributor who sells it, how do I manipulate it so that all of the movies are listed with their respective highest movie price along with the distributor who sells it?

/*finds the cheapest price for the movie "American Beauty" and the distributor who sells it*/
SELECT movies.title, movie_distributors.distributor_name, MIN(distributed_movie_list.unit_price) AS lowest_price 
FROM distributed_movie_list 
INNER JOIN movies ON distributed_movie_list.movie_id = movies.movie_id 
INNER JOIN movie_distributors ON movie_distributors.distributor_id = distributed_movie_list.distributor_id 
WHERE movies.title = 'American Beauty' 
AND distributed_movie_list.unit_price = 
    (SELECT MIN(unit_price) 
    FROM distributed_movie_list 
    INNER JOIN movies ON distributed_movie_list.movie_id = movies.movie_id 
    WHERE movies.title = 'American Beauty') 
GROUP BY movies.title, distributed_movie_list.distributor_id, movie_distributors.distributor_name;

The following tables are used for this query:

create table movies (
movie_id              number(5),
title                 varchar2(30) not null,
category_code         char(3) not null,
description           varchar2(500),
released_by           number(3) not null,
released_on           date not null
);

create table movie_distributors(
distributor_id      number(3),
distributor_name    varchar2(30) not null,
location            varchar2(40),
contact             varchar2(40)
);

create table distributed_movie_list(
distribution_id           number(8),
movie_id                  number(5) not null,
distributor_id            number(3) not null,
distribute_type           varchar2(10),
inventory_quantity        number(3) default 0,
unit_price                number(8,2)
);

The end result I'm looking for is a query that list distributors and highest prices for each movie. Any help would be greatly appreciated ; )

Upvotes: 1

Views: 10518

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

If you are trying to get the highest price for each movie -- and associated information -- then use the row_number() function. The following query returns all the information about the highest price for each movie:

select dml.*
from (select dml.*,
             ROW_NUMBER() over (partition by movie_id order by unit_price desc) as seqnum
      from distributed_movie_list dml 
     ) dml
where seqnum = 1

You can join in other information that you want about movies and distributors.

Upvotes: 1

user359040
user359040

Reputation:

The following should work on most RDBMSs:

SELECT DISTINCT m.title, md.distributor_name, dml.unit_price AS highest_price 
FROM distributed_movie_list dml
INNER JOIN movies m ON dml.movie_id = m.movie_id 
INNER JOIN movie_distributors md ON md.distributor_id = dml.distributor_id 
INNER JOIN (SELECT movie_id, MAX(unit_price) 
            FROM distributed_movie_list
            GROUP BY movie_id) dmlh
        ON dml.unit_price = dmlH.unit_price AND dml.movie_id = dmlh.movie_id

A more efficient solution should be possible for RDBMSs (such as Oracle or SQLServer) that support ranking functions.

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

The end result I'm looking for is a query that list distributors and highest prices for each movie

Then why not just GROUP BY title, distributor_name with MAX like so:

SELECT 
  m.movie_id,
  m.title,
  md.distributor_name, 
  MAX(d.unit_price) AS Highest_price 
FROM distributed_movie_list   AS d
INNER JOIN movies             AS m  ON d.movie_id        = m.movie_id 
INNER JOIN movie_distributors AS md ON md.distributor_id = d.distributor_id 
GROUP BY m.movie_id,m.title,
         md.distributor_name
HAVING MAX(d.unit_price) = (SELECT MAX(d2.unit_price)
                            FROM  distributed_movie_list d2
                            WHERE m.movie_id = d2.movie_id)

Upvotes: 2

Related Questions