Given this data, where there can be multiple events on a date:
sqlite> select id, date, name from event;
1|2025-04-30|april event
2|2025-05-02|may 2 event
3|2025-05-01|May Day morning
4|2025-05-01|May Day tour
I'm trying to construct prev/next links for the events such that
getPrev(1) returns null and getNext(1) returns 3
getPrev(2) returns 4 and getNext(2) returns null
getPrev(3) returns 1 and getNext(3) returns 4
getPrev(4) returns 3 and getNext(4) returns 2
(although actually I don't care about the order of two events on the same day)
Previous attempts include the naive
SELECT 'prev for 3 is:', id, date, name
FROM event
WHERE date <= '2025-05-01'
AND id != 3
ORDER BY date DESC, id DESC
LIMIT 1;
SELECT 'next for 3 is:', id, date, name
FROM event
WHERE date >= '2025-05-01'
AND id != 3
ORDER BY date ASC, id ASC
LIMIT 1;
which produces the same prev/next for both events on May 1
prev for 3 is:|4|2025-05-01|May Day tour
next for 3 is:|4|2025-05-01|May Day tour
I'm pretty sure the answer involves a subselect, but this
SELECT 'prev for 3 is:', id, date, name FROM (
SELECT id, date, name
FROM event
WHERE date <= '2025-05-01'
AND id != 3
ORDER BY date DESC, id DESC
LIMIT (
select count(*)
from event
where date = '2025-05-01'
)
)
ORDER BY id DESC
LIMIT 1;
SELECT 'next for 3 is:', id, date, name FROM (
SELECT id, date, name
FROM event
WHERE date >= '2025-05-01'
AND id != 3
ORDER BY date ASC, id ASC
LIMIT (
select count(*)
from event
where date = '2025-05-01'
)
)
ORDER BY id ASC
LIMIT 1;
(and repeated for event.id 4) yields the wrong links:
prev for 3 is:|4|2025-05-01|May Day tour
next for 3 is:|2|2025-05-02|may 2 event -- either this one should be #4 May Day tour
prev for 4 is:|3|2025-05-01|May Day morning
next for 4 is:|2|2025-05-02|may 2 event -- or this one should be #3 May Day Morning
I believe the problem is that I can't impose an ordering constraint like a "where id < ?" because ids are ordered independently of the dates, so I end up with events on the same day in a bag.
I was able to solve the problem in code, but I was wondering if there was a SQL solution I was missing.
You can use a LAG and LEAD function directly to get the immediate previous and next events.
You can see LAG(id,1)
is used which is used to just return the immediate previous or next date(LEAD(id,1)
) based on sorted dates and id(id is used in ORDER BY since there are multiple events in a single day).
SELECT id,date,name,
LAG(id, 1) OVER (ORDER BY date,id) AS prev_id,
LEAD(id, 1) OVER (ORDER BY date,id) AS next_id
FROM event
ORDER BY id;
Output
id | date | name | prev_id | next_id |
---|---|---|---|---|
1 | 2025-04-30 | april event | null | 3 |
2 | 2025-05-02 | may 2 event | 4 | null |
3 | 2025-05-01 | May Day morning | 1 | 4 |
4 | 2025-05-01 | May Day tour | 3 | 2 |