0

The statement works for me:

SELECT e.id, e.title, array_agg(d.start_date) date, array_agg(d.id) ids
FROM event e JOIN event_date d ON e.id = d.event_id
GROUP BY e.id

I receive the results

id title dates ids
1 First Event {2022-06-05,2022-10-05} {1,2}
2 Second Event {2022-07-05} {3}

I want to order events by start_date. For that, I add ORDER BY d.start_date DESC to the statement:

SELECT e.id, e.title, array_agg(d.start_date) date, array_agg(d.id) ids
FROM event e JOIN event_date d ON e.id = d.event_id
GROUP BY e.id
ORDER BY d.start_date DESC

And I receive the error message:

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

I don't understand it. array_agg is an aggregate function. How to solve this issue?

olek07
  • 513
  • 2
  • 7
  • 21
  • Do you want the values in the array to be sorted? –  Aug 20 '22 at 15:15
  • no, I want the event to be sorted by start_date – olek07 Aug 20 '22 at 15:35
  • "d.start_date" is used in an aggregate function, but it is *also* used in an ORDER BY, which is not an aggregate. – jjanes Aug 20 '22 at 15:41
  • 2
    Since an event can have multiple start_date, which one do you want it sorted by? – jjanes Aug 20 '22 at 15:43
  • I want an event, not a start_date. First Event has to start_dates {2022-06-05,2022-10-05} – olek07 Aug 20 '22 at 15:54
  • look here https://www.db-fiddle.com/f/gxyr5QhFXi3ReJ5UvQ9tH6/0 I want to group by event id and aggregate start_dates – olek07 Aug 20 '22 at 16:02
  • 1
    That's precisely what jjanes asked. You want to sort the events by start_date - but by **which** start_date if there are multiple for each event. In your sample data the first event has the two start dates. One before the start date of the second even and one after the start date of the second event. What is the sort order you are after? –  Aug 20 '22 at 17:05
  • I doesn't matter how many start days has an event. I want to sort all start days of all events. Look here the column start_date – olek07 Aug 20 '22 at 20:45
  • "*I want to sort all start days of all events*" does not make any sense, given your current query which returns only on row for each event. You should [edit] your question and add some sample input data and the expected output based on that. –  Aug 20 '22 at 21:17
  • please look here https://www.db-fiddle.com/f/gxyr5QhFXi3ReJ5UvQ9tH6/0 – olek07 Aug 20 '22 at 21:18

1 Answers1

0

it seems to work

SELECT e.id, e.title, array_agg(d.start_date ORDER BY d.start_date ASC) AS dates 
FROM event e JOIN event_date d ON e.id = d.event_id 
GROUP BY e.id 
ORDER BY dates ASC
olek07
  • 513
  • 2
  • 7
  • 21