CODEWITHSUNDEEP
mysqlsql
online.0227
online.0227

Reputation: 648

SQL "group by" doesn't work

If there is follwing table:

offer(title, price, theater)

The purpose of this table is to describe theaters that offer their movies at their prices.

What I want to show is that:

for each movie, show its price that is offered at highest price by one of all theaters, also shows theater name that offers it at highest price.

So here, I wrote:

select A.movie, max(A.price), A.theater from offer A group by A.movie

However, this doesn't work. How can I show distinct title along with its price and theater that offers it at highest prices among all the theaters?

Upvotes: 1

Views: 6371

Answers (4)

Marco Salerno
Marco Salerno

Reputation: 5201

You said that offer table, just has

title, price and theater

Columns.

If you didn't make an error by writing the question, the problem is that the column movie doesn't exists

Upvotes: 0

Gab
Gab

Reputation: 3520

Here is how you can write the query to find the highest price and its corresponding theatre:

SELECT o.movie,
       (
            SELECT MAX(price)
            FROM offer
            WHERE movie = o.movie
       ) as price,
       (
            SELECT theater
            FROM offer
            WHERE movie = o.movie
            ORDER BY price DESC
            LIMIT 1
       ) as theater
FROM offer o;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

A typical way to do this uses row_number():

select o.movie, o.price, o.theater
from (select o.*,
             row_number() over (partition by movie order by price desc) as seqnum
      from offer o
     ) o
where seqnum = 1;

Note that you can do this without a subquery in Oracle:

select movie, max(price),
       max(theater) keep (dense_rank first order by price desc) as theater
from offer o
order by movie;

Upvotes: 0

OldProgrammer
OldProgrammer

Reputation: 12179

select A.movie, A.theater, max(A.price)
from offer A
 group by A.movie,  A.theater

Upvotes: 0

Related Questions