`order by` et `group` by in an `inner join` SQLite query

Question

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.

zakuarbor · Accepted Answer · 2023-08-15T18:50:55.380

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

Thanks a lot. I understand. However, at the end, I'd like a query result to be (distinct): `id_2`, `id_1`, because, in plain english: the first next upcoming event is `id_2` (because one of its occurrence is 2023-08-29, and after it's `id_1` (because the first next occurrence is 2023-09-01). Any idea? — charnould, Aug 15 '23 at 18:07
Maybe try: ```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``` Edit: I see why you used `group by` now, you wanted to use `min` which you mentioned in your question. — zakuarbor, Aug 15 '23 at 18:41
Perfect! It works. But is it efficient/fast in a huge DB (theory)? — charnould, Aug 15 '23 at 21:02
I am no database expert but I would assume a database like SQLite would have no issues going through millions of records with this SQL command. The query is quite simple with just one join and provided you have `id` as an indexable column ~and start set with the type `date` if it exists in SQLite,~ should be efficient. You could try using `explain` command to see what the database is doing. — zakuarbor, Aug 16 '23 at 00:16

`order by` et `group` by in an `inner join` SQLite query

1 Answers1