Select before and after row with uuid-CodePudding

I have the following table, sorted by pageNumer, exerciseNumber:

| uuid                                 | pageNumber | exerciseNumber | name |
|--------------------------------------|------------|----------------|------|
| 31620782-5866-4198-a285-76cda9568f4d | 1          | 1              | a    |
| 21abdcf6-a99f-4c73-825c-313e0eda2e02 | 2          | 3              | b    |
| 6134609d-aedc-4385-9bf3-08c7a5514448 | 3          | 3              | c    |
| a3d5d839-5fdd-4687-904a-9b0cbec11d68 | 3          | 4              | d    |

I am selecting row with uuid = 6134609d-aedc-4385-9bf3-08c7a5514448, how can I select before row and after row of selected row?

Normally I would do this by adding or subtracting 1 from id. But here I have uuid, which is not auto-increment :(

CodePudding user response：

You must first add an id column

You can use this mode later: ‌

select * from bar where id = (select min(id) from bar where id > 4)

select * from bar where id = (select max(id) from bar where id < 4)

CodePudding user response：

You can use ROW_NUMBER() window function to rank the rows of the table and a self join:

WITH cte AS (SELECT *, ROW_NUMBER() OVER (ORDER BY pageNumber, exerciseNumber) rn FROM tablename)
SELECT c1.uuid, c1.pageNumber, c1.exerciseNumber, c1.name
FROM cte c1 INNER JOIN cte c2
ON c2.rn IN (c1.rn - 1, c1.rn   1)
WHERE c2.uuid = '6134609d-aedc-4385-9bf3-08c7a5514448';

See the demo.