Get top N items per group in Ruby on Rails

Question

I have a model with the fields "date" and "frequency" (Frequency is an integer). I'm trying to get the top 5 frequencies per date. Essentially I want to group by date, then get the top 5 per group.

What I have so far only retrieves the top 1 in the group:

Observation.channel("channelOne").order('date', 'frequency desc').group(:date).having('frequency = MAX(frequency)')

I want the MAX(frequency) PLUS the second, third, fourth and fifth largest PER DATE.

Sorry if this is really simple or if my terminology is off; I've just started with rails :)

Answer 1

You can use this:

 Observation
   .select("obs1.*")
   .from("observations obs1")
   .joins("LEFT JOIN observations AS obs2 ON obs1.date = obs2.date AND obs1.frequency <= obs2.frequency")
   .group("obs1.date, obs1.id")
   .having("count(*) <= 5")
   .order("obs1.date, obs2.frequency")

This query returns the top 5 frequencies for each date.