Retrieve next/previous record from MySQL query sorted by multiple columns

Question

I'm running a query through my MySQL database (MariaDB10.3) like this: SELECT*FROMmy_tableORDERBYpriorityDESC,expiration_dateASC,idASC An example of this table with the given order looks like this: id...priority expiration_date3...22022- 07-0112:00:007...22022-07-0312:00:006...22022-07-0

P粉763748806 · Answer

Following Paul Maxwell's tips about LEAD and LAG, I was able to write a working query like this:

SELECT t.next_id 
FROM (
    SELECT id, 
        LEAD(id, 1) OVER (ORDER BY priority DESC, expiration_date ASC, id ASC) AS next_id 
    FROM my_table
) t 
WHERE t.id = ?

Retrieve previous records corresponding to LAG.

id	...	priority	expiration_date
3	...	2	2022-07-01 12:00:00
7	...	2	2022-07-03 12:00:00
6	...	2	2022-07-04 12:00:00
9	...	1	2022-07-02 12:00:00
4	...	1	2022-07-05 12:00:00
11	...	1	2022-07-05 12:00:00