How to get the first record for several months

advertisements

I have a Placements table which keeps track of employees and their points per month.

I am trying to work out how to get the employee that finished in first place for each of the months.

I've tried something like this

create_table "placements", :force => true do |t|
 t.integer  "employee_id"
 t.date     "month"
 t.integer  "points"
end

@previous_winners = @placements.includes(:employee).order('points DESC').first

I think I might need to loop through all months?


The 'GROUP BY' works a little differently in PostgreSQL than in MySQL. What you are looking for is rails/ruby equivalent for the following SQL expression:

SELECT DISTINCT ON (month) month,points, employee_id
FROM placements
order by month, points DESC

In rails controller, you can get the above using:

@placements.select('DISTINCT ON (month) month, points, employee_id').where('YOUR_CONDITION').order('month, points DESC')

This is just an example on how to group in PostgreSQL, cater it to your needs.

PS: 'DISTINCT ON' is the trick here :)