7

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

orrymr
  • 2,264
  • 4
  • 21
  • 29
  • Which database are you using ? – romainsalles Sep 30 '15 at 15:28
  • @romainsalles -> mysql – orrymr Sep 30 '15 at 15:43
  • I think this is more an SQL problem. See the ```ROW_NUMBER()``` function in mysql (http://stackoverflow.com/questions/1895110/row-number-in-mysql) and combine it with the "find_by_sql" ActiveRecord method (http://apidock.com/rails/ActiveRecord/Base/find_by_sql/class). You should be able to determine the appropriate request to retrieve your 5 most frequent Observations by date. – romainsalles Sep 30 '15 at 15:54
  • I'm more a Postgres guy and I can't test this request, but I think something like that could work : ```Observation.find_by_sql(" SELECT date, frequency FROM ( SELECT frequency, date AS created_at, id, ROW_NUMBER() OVER (PARTITION BY date ORDER BY date DESC, frequency DESC) AS rank FROM observations where channel = 'channelOne' ) AS o WHERE rank <= 5 ORDER BY date DESC, frequency DESC ")``` – romainsalles Sep 30 '15 at 16:02

1 Answers1

6

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.

Rodrigo
  • 5,435
  • 5
  • 42
  • 78
  • Thanks! Question: This orders the frequencies in ascending order. I need them in descending, in order for me to get the top 5. I tried .order("obs1.date, obs2.frequency desc") (a guess) which didn't work. Also - could you point me to a tutorial on this? That would be greatly appreciated :) – orrymr Oct 01 '15 at 08:38
  • @orrymr, I used this response as resource: http://stackoverflow.com/a/12114175/740394 – Rodrigo Oct 01 '15 at 13:01