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 :)