Could you help me please? I have a table List:

        cinema char (15)
        movie char (20))

I need to get movies which are shown in most cinemas and the number of times they are shown. If there are more then one movie shown the same number of times, there must be all of them.

All I wrote yet is:

SELECT MAX(CNT.cout) as  maximum
     FROM (SELECT movie, COUNT(ALL cinema) as cout FROM List GROUP BY movie) as CNT

but it just shows the number of times the most shown move was showned...

Edited by yohgurt

4 Years
Discussion Span
Last Post by yohgurt

I also tried

FROM (SELECT movie, COUNT(cinema) as cout FROM PC GROUP BY movie) as CNT INNER JOIN List
ON CNT.moviel = List.movie
GROUP BY CNT.movie, CNT.cout, PC.cinema
HAVING CNT.cout=(SELECT max(CONT.cinemas) as Maximum
                  FROM(SELECT movie, COUNT(cinema) as cinemas 
                        FROM List 
                        GROUP BY movie) as CONT)

but it doesn't seem working

UPD: OMG, it worked! Sorry to bother you, it's just I spent a few hours to get it.

Edited by yohgurt

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.