sql - Rails with postgres - activerecord query: sort by column ASC, then group by column -

i have model laps, belongs :car

so each car has many laps, need query pull in top 10 fastest laps, pull in fastest lap per each car. in other words, not want 1 car allowed have 5 of top 10 fastest laps in list.

each lap has :car_id field. want group on column, , pull out lap min(lap.time) row grouping. (aka fastest lap time unique :car_id).

so postgres, best approach this? can order laps first, group, , pull first group. or grouping not keep sort order?

here schema lap model:

  create_table "laps", force: true |t|     t.decimal  "time"     t.string   "video_url"     t.integer  "car_id"     t.datetime "created_at"     t.datetime "updated_at"     t.integer  "user_id"     t.boolean  "approved"   end 

do have use 2 combined queries this?

i can unique car id's lap doing this:

select('distinct on (car_id) *') 

but, need order laps gets min(lap.time) per car_id. when throw on order this:

select('distinct on (car_id) *').order('car_id, time asc').sort_by! {|ts| ts.time} 

this works, seems odd way go it. try , change around order, removing car_id order, postgres errors.

as you're discovering, distinct on isn't going work here because doesn't match first term want sort on (time). you'll need use group by:

lap.group(:car_id).limit(10).minimum(:time) 

alternatively, can make windowed subquery - quite bit messier build. if need actual lap information aside time, may have go route:

subquery = lap.select('lap.*, row_number() on ( partition car_id order time, car_id ) rownum').to_sql lap.scoped.from(arel.sql("(#{subquery}) fast_laps"))    .where('rownum = 1')    .order('time, car_id')    .limit(10)