From multiple queries into one query in SQL

Question

My database "Films" contains columns: name, year (1980-2015) and rating of the film (1-10). I would like to get the BEST FILM from EVERY year like:

The Beutiful Mind 2001 rating: 8.2
Catch Me If You Can 2002 rating: 8.0

I only can think how to do it in multiple queries like:

SELECT *
FROM movies
WHERE year = 2001
ORDER BY imdb_rating DESC
LIMIT 1;

SELECT *
FROM movies
WHERE year = 2002
ORDER BY imdb_rating DESC
LIMIT 1;

etc.

How can i achieve it in one query in SQL? What's the best way? Or maybe there is no problem with multiple queries in this case? Thanks for help :)

what happens if there are 2 films with a rating of 10 on the same year? — Webeng
– Webeng, Commented May 10, 2016 at 0:48
Webeng - we can just choose one from the best; SQLChao, Gordon Linoff - i am just practising SQL queries, sorry for adding other tags :) — gooornik07
– gooornik07, Commented May 10, 2016 at 0:55

Gordon Linoff · Accepted Answer · 2016-05-10 00:50:40Z

3

One method is to use a subquery in the where clause:

select m.*
from movies m
where m.imdb_rating = (select max(m2.rating) from movies m2 where m2.year = m.year);

answered May 10, 2016 at 0:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dcieslak · Accepted Answer · 2016-05-10 00:49:08Z

2

select name, year from (
select 
  name, year,
  row_number() over (partition by year order by rating desc) as rt
from movies ) RankTab
where rt = 1

answered May 10, 2016 at 0:49

dcieslak

2,7151 gold badge15 silver badges20 bronze badges

Webeng · Accepted Answer · 2016-05-10 01:51:47Z

0

SELECT name, year, MAX(rating) FROM Films WHERE GROUP BY year

Let me know if that worked for you.

answered May 10, 2016 at 1:51

Webeng

7,0984 gold badges37 silver badges61 bronze badges

SELECT name, year, MAX(rating) FROM Films GROUP BY year; <--- quite simple and gives me expected results, thank You :)