CODEWITHSUNDEEP
sqldatabasepostgresql
TheKidsWantDjent
TheKidsWantDjent

Reputation: 1269

Is there an easier way to find the row with a max value?

I have a schema where these two tables exist (among others)

participation

+------+--------+------------------+
| movie| person |       role       |
+------+--------+------------------+
|    1 |      1 | "Regisseur"      |
|    1 |      1 | "Schauspieler"   |
|    1 |      2 | "Schauspielerin" |
|    2 |      3 | "Regisseur"      |
|    3 |      4 | "Regisseur"      |
|    3 |      5 | "Schauspieler"   |
|    3 |      6 | "Schauspieler"   |
|    4 |      7 | "Schauspielerin" |
|    4 |      8 | "Schauspieler"   |
|    5 |      1 | "Schauspieler"   |
|    5 |      8 | "Schauspieler"   |
|    5 |     14 | "Schauspieler"   |
+------+--------+------------------+

movie

+----+------------------------------+------+-----+
| id |            title             | year | fsk |
+----+------------------------------+------+-----+
|  1 | "Die Bruecke am Fluss"       | 1995 |  12 |
|  2 | "101 Dalmatiner"             | 1961 |   0 |
|  3 | "Vernetzt - Johnny Mnemonic" | 1995 |  16 |
|  4 | "Waehrend Du schliefst..."   | 1995 |   6 |
|  5 | "Casper"                     | 1995 |   6 |
|  6 | "French Kiss"                | 1995 |   6 |
|  7 | "Stadtgespraech"             | 1995 |  12 |
|  8 | "Apollo 13"                  | 1995 |   6 |
|  9 | "Schlafes Bruder"            | 1995 |  12 |
| 10 | "Assassins - Die Killer"     | 1995 |  16 |
| 11 | "Braveheart"                 | 1995 |  16 |
| 12 | "Das Netz"                   | 1995 |  12 |
| 13 | "Free Willy 2"               | 1995 |   6 |
+----+------------------------------+------+-----+

I want to get the movie with the highest number of people that participated. I figured out an SQL statement that actually does this, but looks super complicated. It looks like this:

SELECT titel 
FROM   movie.movie 
       JOIN (SELECT * 
             FROM   (SELECT Max(count_person) AS max_count_person 
                     FROM   (SELECT movie, 
                                    Count(person) AS count_person 
                             FROM   movie.participation 
                             GROUP  BY movie) AS countPersons) AS 
                    maxCountPersons 
                    JOIN (SELECT movie, 
                                 Count(person) AS count_person 
                          FROM   movie.participation 
                          GROUP  BY movie) AS countPersons 
                      ON maxCountPersons.max_count_person = 
                         countPersons.count_person) 
                               AS maxPersonsmovie 
         ON maxPersonsmovie.movie = movie.id

The main problem is, that I can't find an easier way to select the row with the highest value. If I simply could make a selection on the inner table and pick the row with the highest value on count_person without losing the information about the movie itself, this would look so much simpler. Is there a way to simplify this, or is this really the easiest way to do this?

Upvotes: 1

Views: 53

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

Here is a way without subqueries:

SELECT m.title
FROM movie.movie m JOIN
     movie.participation p
     ON m.id = p.movie
GROUP BY m.title
ORDER BY COUNT(*) DESC
FETCH FIRST 1 ROW ONLY;

You can use LIMIT 1 instead of FETCH, if you prefer.

Note: In the event of ties, this only returns one value. That seems consistent with your question.

Upvotes: 1

Ivan Mogila
Ivan Mogila

Reputation: 467

SELECT title
FROM   movie.movie
WHERE  id = (SELECT   movie
             FROM     movie.participation
             GROUP BY movie
             ORDER BY count(*) DESC
             LIMIT 1);

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use rank window function to do this.

SELECT title
FROM (SELECT m.title,rank() over(order by count(p.person) desc) as rnk
      FROM movie.movie m
      LEFT JOIN movie.participation p ON m.id=p.movie
      GROUP BY m.title
     ) t
WHERE rnk=1

Upvotes: 0

Related Questions