1

I have following ids in my table.

[25,26,36]

I am able to fetch next and prev id easily if current id is 26 but I am not able to get next and prev id if current id 36. It is giving me a null record of next. Is there any possibility to give me id circularly. Here is my code which I use for next and prev id getting.

next record:

SELECT * FROM foo WHERE id > 26 ORDER BY id LIMIT 1;

previous record:

SELECT * FROM foo WHERE id < 26 ORDER BY id DESC LIMIT 1;
Jaydip Satvara
  • 138
  • 1
  • 15
  • what kind of dbms are you using? – hotfix Aug 09 '18 at 07:03
  • Possible duplicate of [How to get next/previous record in MySQL?](https://stackoverflow.com/questions/1446821/how-to-get-next-previous-record-in-mysql) – hotfix Aug 09 '18 at 07:06
  • @hotfix my question is different. As you sent a link which will not perfect solution which I want. I already mention my requirement in question. – Jaydip Satvara Aug 09 '18 at 09:06

3 Answers3

1

Use LAG/LEAD to get prev/next value unless it's not supported by sql engine.

SELECT *, 
       LAG(Id) OVER(ORDER BY Id) AS PreValue, 
       LEAD(Id) OVER(ORDER BY Id) AS NextValue
FROM (VALUES (25), (26), (36)) AS T(Id)

gives result

Id  PreValue    NextValue
25  NULL        26
26  25          36
36  26          NULL

and then fall back to max/min id when it's value

SELECT *,
    COALESCE(LAG(Id) OVER(ORDER BY Id), MAX(Id) OVER()) AS PreValue, 
    COALESCE(LEAD(Id) OVER(ORDER BY Id), MIN(Id) OVER()) AS NextValue
FROM (VALUES (25), (26), (36)) AS T(Id)

Note, the default partition is whole table. When you have a your own partition, keep them sync in four OVER clause.

qxg
  • 6,955
  • 1
  • 28
  • 36
0

Try:

select a.id, CASE WHEN lag(a.id)over(order by a.v) is null then max(b.id) else lag(a.id)over(order by a.v) end as pr,
CASE WHEN  lead(a.id)over(order by a.v) is null then min(b.id) else lead(a.id)over(order by a.v) 
end as n 
from t1 a , t1 b
group by a.id,a.v

column 'v' is a primary key

Output: http://sqlfiddle.com/#!18/eb72b/22

akshay
  • 777
  • 4
  • 15
0

You can do this by moving the filter condition to the ORDER BY:

SELECT *
FROM foo
ORDER BY (CASE WHEN id > 26 THEN id END) ASC,
         ID ASC
LIMIT 1;

Similarly for the previous one:

SELECT *
FROM foo
ORDER BY (CASE WHEN id < 26 THEN id END) DESC,
         ID DESC
LIMIT 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yes, But I want the result in one query. – Jaydip Satvara Aug 09 '18 at 12:19
  • @jaydipsatvara . . . The question you have asked is: "Is there any possibility to give me id circularly?" This answers that question. If you have a different question, I would suggest that you ask *another* question, provide sample data, desired results, and a clear explanation of what you want to do. – Gordon Linoff Aug 09 '18 at 12:21