GROUP BY after ORDER BY/LIMIT/OFFSEt?

Question

I have two tables events and eventdate, where eventdate has a row event that corresponds to the id column in events. (So an event can have multiple dates). `

Now I am trying to get the unique events of the first five eventdates after a certain date. I tried this query:

SELECT ed."event"
    FROM public."eventdate" as ed
    GROUP BY ed."event"
    ORDER BY ed."start_date" ASC
    LIMIT 5
    OFFSET 0

but the following error is thrown:

ERROR: column "ed.start_date" must appear in the GROUP BY clause or be used in an aggregate function
LINE 4: ORDER BY ed."start_date" DESC

To be clear, I expect a list of ids, the list should have at maximum 5 entries, and no duplicates (a duplicate would just be "removed").

Given the following set:

id | event | start_date
1  | 1     | 0
2  | 2     | 0
3  | 1     | 2
4  | 4     | 3
5  | 3     | 4
6  | 1     | 5
7  | 5     | 6
7  | 6     | 6

The result would be:

event
1 (if I'd count the ids here it would be "2")
2
4
3

so basically the invariant would be: summing the count of each group = 5

Answer 1

If I get it right, you can use window functions:

select event
from (
    select ed.event, ed.start_date
         , row_number() over (partition by ed.event order by ed.start_date) as rn
    from public.eventdate
) as x
where rn = 1
order by start_date
limit 5 

In the inner select, the events are given an ordering number per event. In the outer select, the oldest one per event is chosen and the result is ordered by this date.

If this is not what you meant, you can change your query so that the order by becomes deterministic, by adding an aggregate function (I used MIN here):

SELECT ed."event"
FROM public."eventdate" as ed
GROUP BY ed."event"
ORDER BY MIN(ed."start_date") ASC
LIMIT 5
OFFSET 0