Select neighboring entries-CodePudding

I have a table with text notes. My app shows the entries sorted by pinned_flag then by note_date :

id | note_text | note_date | pinned_flag
----------------------------------------
1  | note1     | 01/04/2021 |  1 
----------------------------------------
3  | note2     | 01/02/2021 |  1 
----------------------------------------
5  | note3     | 01/05/2021 |  0 
----------------------------------------
4  | note4     | 01/03/2021 |  0 
----------------------------------------
2  | note5     | 01/01/2021 |  0
----------------------------------------

I need to select 1 record below and 1 record above the record with the given id. (for entry 5 the result will be 3 and 4) What queries can I use to do this?

CodePudding user response：

If your version of SQLite is 3.25.0 you can do it with ROW_NUMBER() window function:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY pinned_flag DESC, note_date DESC) rn 
    FROM tablename
)
SELECT id, note_text, note_date, pinned_flag
FROM cte
WHERE (SELECT rn FROM cte WHERE id = 5) IN (rn - 1, rn   1);

If you can't use window functions but your version of SQLite is 3.15.0 you can use UNION ALL and Row Values:

WITH cte AS (SELECT * FROM tablename WHERE id = 5)
SELECT * FROM (
  SELECT * FROM tablename
  WHERE (pinned_flag, note_date) > (SELECT pinned_flag, note_date FROM cte)
  ORDER BY pinned_flag, note_date LIMIT 1
)
UNION ALL
SELECT * FROM (
  SELECT * FROM tablename
  WHERE (pinned_flag, note_date) < (SELECT pinned_flag, note_date FROM cte)
  ORDER BY pinned_flag DESC, note_date DESC LIMIT 1
);

For previous versions:

WITH cte AS (SELECT * FROM tablename WHERE id = 5)
SELECT * FROM (
  SELECT * FROM tablename
  WHERE pinned_flag > (SELECT pinned_flag FROM cte)
     OR (pinned_flag = (SELECT pinned_flag FROM cte) AND note_date > (SELECT note_date FROM cte))
  ORDER BY pinned_flag, note_date LIMIT 1
)
UNION ALL
SELECT * FROM (
  SELECT * FROM tablename
  WHERE pinned_flag < (SELECT pinned_flag FROM cte)
     OR (pinned_flag = (SELECT pinned_flag FROM cte) AND note_date < (SELECT note_date FROM cte))
  ORDER BY pinned_flag DESC, note_date DESC LIMIT 1
);

See the demo.