Considering below SQLite DB tables and this DBFiddle:
events table
id | text
---------
id_1 | hello
id_2 | world
calendar table
id | start
---------------
id_1 | 2023-01-01
id_1 | 2023-06-01
id_1 | 2023-09-01
id_1 | 2023-10-01
id_2 | 2023-05-01
id_2 | 2023-08-29
id_2 | 2023-10-30
id_2 | 2023-11-30
How do I simply get a list of all next coming events ordered by calendar.start?
Utimately, I'll add some where clause on events table (e.g. where status = 'published')
I tried:
select * from events
inner join calendar using (id)
where calendar.start > STRFTIME('%Y-%m-%d', 'now')
group by id
But I'm struggling with the order by (cannot be after group by or before). I tried with a min but it seems complicated. Should I go with distinct on calendar table? Thanks.
get a list of all next coming events ordered by calendar.start
Based on my understanding of what you want to achieve, you should not use group by. Group By are for aggregating data such as counting the number of columns or adding a column together.
Your statement would only return 2 rows (i.e. the number of events which is 2 according to your table.
select * from events
inner join calendar using (id)
where calendar.start > STRFTIME('%Y-%m-%d', 'now')
ORDER BY calendar.start
will give you a list of calendars associated with reoccurring events
i.e.
id text start
--------------------------
id_2 world 2023-08-29
id_1 hello 2023-09-01
id_1 hello 2023-10-01
id_2 world 2023-10-30
id_2 world 2023-11-30
Group by as I mentioned earlier is to aggregate rows (at least it usually is). For instance, let's grab the number of meetups for each event remaining:
select calendar.id, events.text, count(*) as num_events_remaining
from events
inner join calendar using (id)
where calendar.start > STRFTIME('%Y-%m-%d', 'now')
group by id
which results:
id text num_events_remaining
------------------------------------
id_1 hello 2
id_2 world 3
Edit:
If you want a table that grabs the next upcoming meeting for each event, the SQL query, then you are correct to use group by. I should have caught what you were trying to do when you said you experimented with min.
SELECT id, text, min(start) AS upcoming
FROM calendar
INNER JOIN events using (id)
WHERE calendar.start > STRFTIME('%Y-%m-%d', 'now')
GROUP BY id
ORDER BY upcoming
Would give you:
id text upcoming
--------------------------
id_2 world 2023-08-29
id_1 hello 2023-09-01
Similarly if you want the last meeting for each event, you would use max:
SELECT id, text, max(start) AS last_date
FROM calendar
INNER JOIN events using (id)
WHERE calendar.start > STRFTIME('%Y-%m-%d', 'now')
GROUP BY id
ORDER BY last_date
which gives you:
id text last_date
--------------------------
id_1 hello 2023-10-01
id_2 world 2023-11-30