0

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

  • Yes, but which of the many start_dates that belong to one event group should be used for sorting? – Laurenz Albe Sep 2 at 8:24
  • @LaurenzAlbe well no, it should first order & limit by the start_dates of the ungrouped set, and then, only after order by has run, group the remaining items. – paul23 Sep 2 at 8:25
  • Do you want 5 distinct events in the result, ordered by the oldest start_date per event? – Lennart Sep 2 at 8:28
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.