fetch 5 items for each day in a given month, in raw sql or Django orm

Question

Suppose an event is associated with a date.

For a given month,

I'd like to retrieve events happening in that month,

with a constraint such that:

I want only 5 events at max for each day.

Answer 1

Most databases support the ANSI standard window functions. So, this would be written as:

select t.*
from (select t.*,
             row_number() over (partition by thedate
                                order by thedate) as seqnum
      from t
      where date >= @date1 and date < @date2
     ) t
where seqnum <= 5

@date1 and @date2 are just place holders for the values that define the beginning and end of the period you care about.

fetch 5 items for each day in a given month, in raw sql or Django orm

Sign up