CODEWITHSUNDEEP
mysqlsql
hubvoy
hubvoy

Reputation: 31

How can I select championship winners in MySQL

I'm doing a couple of exercises in preparation for my exams, and I stumbled upon a problem in one concerning SQL. I have a database with information about Formula 1 drivers, races, and results.

I am trying to select first and last names of championship winners from years 2000, 2006, 2012, along with the sum of their points in years where they won. I ran the following query, which gives me the sum of points for each driver in each year, but I don't know how to only get the top ones from each year.

SELECT season, first_name, last_name, SUM(points)
FROM (drivers JOIN results ON drivers.driver_id = results.driver_id)
JOIN races ON races.race_id = results.race_id
WHERE season IN(2000, 2006, 2012)
GROUP BY season, first_name, last_name;

Upvotes: 0

Views: 482

Answers (2)

Hugo André
Hugo André

Reputation: 118

If i didnt misunderstood your question, you should try this : 

SELECT t.season, t.first_name, t.last_name, t.points
FROM    (
        SELECT  seasons
        FROM (drivers JOIN results ON drivers.driver_id = results.driver_id)
        JOIN races ON races.race_id = results.race_id
        ) a
CROSS APPLY
        (
        SELECT b.season, b.first_name, b.last_name, SUM(c.points) AS points
        FROM (drivers b JOIN results c ON drivers.driver_id = results.driver_id) 
        JOIN races ON races.race_id = results.race_id
        WHERE season IN(2000, 2006, 2012) AND a.seasons = b.season
        ORDER BY points DESC
        LIMIT 1
        ) t

Upvotes: 0

forpas
forpas

Reputation: 164099

First join Results to Races and aggregate to get the total points of drivers for each season and with ROW_NUMBER() window function get the top 1 driver.
Then join to Drivers to get the driver's name:

SELECT g.season, d.last_name, d.first_name, g.points 
FROM (
  SELECT r.season, t.driver_id, SUM(t.points) points,
    ROW_NUMBER() OVER (PARTITION BY r.season ORDER BY SUM(t.points) DESC) rn 
  FROM Results t INNER JOIN Races r
  ON t.race_id = r.race_id
  WHERE r.season IN (2000, 2006, 2012)
  GROUP BY r.season, t.driver_id
) g INNER JOIN Drivers d
ON d.driver_id = g.driver_id AND g.rn = 1

Upvotes: 1

Related Questions