Select rows with maximum column value distinct column

Question

I have a table with data as fleet table data:

I'm trying to get the MAX aircraft_count for each carriercode along with the rest of the columns.

I know this works but I can't get the rest of the columns

select carriercode, max(aircraft_count) as peak
from fleet
group by carriercode;

This is the closest I have come.

select f1.id, distinct(f1.carriercode), f1.aircraft_count, f1."timestamp"
from fleet f1
where f1.aircraft_count = 
    (select max(f2.aircraft_count)
     from fleet f2
     where f1.carriercode = f2.carriercode)
order by f1.aircraft_count desc;

Expected result:

id - carriercode - max_count - timestamp
10 - EIN         - 100       - 2016-03-27 23:07:49.121449
252 -SCO         - 34        - 2016-03-27 23:07:53.282367 

Answer 1

In Postgres, you can use distinct on:

select distinct on (f.carriercode) f.*
from fleet f
order by carriercode, aircraft_count desc;

This returns one row per carriercode, the one with the highest aircraft_count. If there are ties, then an arbitrary row is selected.

If you want all rows with matches, then distinct on doesn't quite work. Instead:

select f.*
from (select f.*,
             dense_rank() over (partition by carrier_code order by aircraft_count desc) as seqnum
      from fleet f
     ) f
where seqnum = 1;