Query associated MAX column with all it's fields in Postgres

Question

I have three database tables:

• car
  • id
• speed
  • id
  • actual_speed
  • car_id
  • gear_id
• gear
  • id

I would like to select max speeds of all cars and the gear they achieve the max speed with. I got to the following query:

SELECT MAX(speed.actual_speed)
FROM car
INNER JOIN speed ON car.id = speed.car_id
GROUP BY car.id;

This query works but doesn't return the gear. If I include gear_id in the select SELECT MAX(speed.actual_speed), speed.gear_id. The database complains that gear_id should be included in group by or aggregate function.

But if I include it in the group by GROUP BY car.id, speed.gear_id the query returns max speeds for all gears which I'm not interested in.

Is there maybe a way to get back all cars with their max speed and the gear they achieve it in?

Answer 1

A simple and portable solution uses a correlated subquery:

select s.*
from speed s
where s.actual_speed = (select max(s1.actual_speed) from speed s1 where s1.car_id = s.car_id)

This would benefit an index on (car_id, actual_speed).

In Postgres, I would recommend distinct on:

select distinct on (car_id) s.*
from speed s
order by car_id, actual_speed desc

Or, if you want to allow ties:

select *
from (
    select s.*, rank() over(partition by car_id order by actual_speed desc) rn
    from speed s
) s
where rn = 1