CODEWITHSUNDEEP
sql
whitebear
whitebear

Reputation: 12423

How to get the prev and next row of target row

For example data like this

ID  name
8   apple 
13  lemon
15  melon
16  banana
18  watermelon
19  peanuts
20  orange
24  hamburger
25  maron

I got the banana by ID.

Select id,name where id = 16

However I would like to get the prev and next row as well.

In this case.

I would like to get 15 melon and 18 watermelon

What is the best practice for this purpose??

Upvotes: 0

Views: 118

Answers (5)

Ankitkumar Tandel
Ankitkumar Tandel

Reputation: 309

try this ...

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID) AS ROWNUMBER,ID,name FROM [table]) AS T WHERE T.ROWNUMBER >= (SELECT ROWNUMBER-1 FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID) AS ROWNUMBER,ID,name FROM [table]) AS U WHERE U.ID = 16) AND T.ROWNUMBER <= (SELECT ROWNUMBER+1 FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID) AS ROWNUMBER,ID,name FROM [table]) AS V WHERE V.ID = 16);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

The following will work whenever the id is not the first one:

select top 3 t.*
from t
where t.id >= (select min(t.id) from t where t.id < 16)
order by t.id;

Upvotes: 0

arturro
arturro

Reputation: 1606

It depends on the database you are using. In Oracle you can use LEAD and LAG functions:

select id, name, 
LAG(id) OVER (order by id) as prev_id,
LAG(name) OVER (order by id) as prev_name,
LEAD(id) OVER (order by id) as next_id,
LEAD(name) OVER (order by id) as next_name
from fruits
order by id;

Upvotes: 0

SeanN
SeanN

Reputation: 663

A general solution that should work in most SQL dialects is:

SELECT id, name
FROM table
WHERE id BETWEEN 
    (SELECT max(id) FROM table WHERE id < 16)
    AND
    (SELECT min(id) FROM table WHERE id > 16)

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

With SQL Server 2012+ you'll find LEAD() and LAG() (Details here, with a link to the other one).

An approach for lower versions might be this:

First I fill your data into a declared table variable (mock-up table)

DECLARE @tbl TABLE(ID INT,name VARCHAR(100));
INSERT INTO @tbl VALUES
 (8,'apple') 
,(13,'lemon')
,(15,'melon')
,(16,'banana')
,(18,'watermelon')
,(19,'peanuts')
,(20,'orange')
,(24,'hamburger')
,(25,'maron');

--The CTE will number your rows sorted after ID

--The joins will read the neighbour rows with a nr one below or one above

WITH GaplessNumbered AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY ID) AS nr
          ,*
    FROM @tbl
)
SELECT prev.name,t1.name,nxt.name
FROM GaplessNumbered AS t1 
LEFT JOIN GaplessNumbered AS prev ON prev.nr=t1.nr-1
LEFT JOIN GaplessNumbered AS nxt ON nxt.nr=t1.nr+1
WHERE t1.ID=16

You might even let the WHERE away.

Upvotes: 1

Related Questions