Race
| RaceID | Year |
|---|---|
| 01 | 2010 |
| 02 | 2011 |
| 03 | 2011 |
| 04 | 2011 |
| 05 | 2012 |
| 06 | 2012 |
| 07 | 2013 |
Results
| ResultID | RaceID | Speed |
|---|---|---|
| A | 01 | 180 |
| B | 02 | 190.5 |
| C | 03 | 185 |
| D | 04 | 170 |
| E | 05 | 200 |
| F | 06 | 190 |
| G | 07 | 220 |
How can i get the MAX speed of each year and GROup it by year??
CodePudding user response：
So you join race and results on the common field i.e. race_id and then pick only year and speed after which you group by year and get max of speed.
SELECT
race.year
, MAX(results.speed) AS max_speed
FROM race
JOIN results ON (race.raceid = results.raceid)
GROUP BY
race.year
CodePudding user response：
For Year with max_Speed & recordist RaceID & ResultID:
select a.Year, a.max_Speed, b.RaceID, b.ResultID from
(select Year, max(Speed) max_Speed from Race a1
left join Results a2 on a2.RaceID = a1.RaceID
group by Year
) a
left join
(select Year, Speed, b1.RaceID, ResultID from Race b1
left join Results b2 on b2.RaceID = b1.RaceID) b
on b.Year = a.Year
and b.Speed = a.max_Speed
order by Year desc
;
For just Year & max_Speed:
select a.Year, a.max_Speed, b.RaceID, b.ResultID from
(select Year, max(Speed) max_Speed from Race a1
left join Results a2 on a2.RaceID = a1.RaceID
group by Year
) a
left join
(select Year, Speed, b1.RaceID, ResultID from Race b1
left join Results b2 on b2.RaceID = b1.RaceID) b
on b.Year = a.Year
and b.Speed = a.max_Speed
order by Year desc
;