how to get previous/next ids

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

Fiddle Demo