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

Question

I'm running the query through my MySQL database (MariaDB 10.3) like this:

SELECT * FROM my_table ORDER BY priority DESC, expiration_date ASC, id ASC

An example of this table with the given order looks like this:

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

Now I have the ID of a specific record and I am trying to retrieve the records that are before/after said record in the query result in the given order via SQL. Suppose I have record ID 6 and I want to return the records with ID 9 and 7 respectively.

Sort by a single unique column, which is easy to get in a single query, but I'm not sure how to handle multiple non-unique columns. Can someone tell me how to achieve this?

Answer 1

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.