CODEWITHSUNDEEP
sql-servert-sql
davor
davor

Reputation: 969

Selecting previous and next row using sp

I want to select previous and next row from table based on current id. I send current id to stored procedure and use this query:

-- previous
select top 1 id from table where id < @currentId order by id desc
-- next
select top 1 id from table where id < @currentId order by id asc

The problem is when I send currentId which is the last id in the table and want to select next row. Then is nothing selected. Same problem for previous row, when I send currentId which is first id in table

Is it possible to solve this in sql without additional queries?

Upvotes: 0

Views: 1261

Answers (2)

Jeremy Wiggins
Jeremy Wiggins

Reputation: 7299

Edit - just realized you always want two rows to be returned, re-working...

Updated code:

SELECT * FROM @t WHERE ID = (   select top 1 id 
                                from @t 
                                where id < 
                                    case 
                                        when exists (select id from @t where id < @ID) 
                                        then @ID 
                                        else (@ID + 1) 
                                    end 
                                order by id desc
                            )

UNION

SELECT * FROM @t WHERE ID = (   select top 1 id 
                                from @t 
                                where id > 
                                    case 
                                        when exists (select id from @t where id > @ID) 
                                        then @ID 
                                        else (@ID - 1) 
                                    end 
                                order by id
                            )

Original Post below, before I realized what exactly you wanted

You were really close, you needed the UNION operator and one small syntax change to your query.

SELECT * FROM [table] WHERE ID = (select top 1 id from @t where id < @ID order by id desc)
UNION
SELECT * FROM [table] WHERE ID = (select top 1 id from @t where id > @ID order by id)

This will return 1 row when the ID you pass it is the first row or last row, 2 rows otherwise.

Test code:

DECLARE @t TABLE
(
    Id int identity,
    Col varchar(200)
)

INSERT INTO @t VALUES('column one')
INSERT INTO @t VALUES('column two')
INSERT INTO @t VALUES('column three')
INSERT INTO @t VALUES('column four')
INSERT INTO @t VALUES('column five')

DELETE FROM @t WHERE ID = 3

DECLARE @Id int
SET @Id = 3 -- Change this to other values to further test

SELECT * FROM @t WHERE ID = (select top 1 id from @t where id < @ID order by id desc)
UNION
SELECT * FROM @t WHERE ID = (select top 1 id from @t where id > @ID order by id)

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453910

You can use a nested top. Example for your "next row" case.

SELECT TOP 1 *
FROM   (SELECT TOP 2 *
        FROM   YourTable
        WHERE  id >= @currentId
        ORDER  BY id) T
ORDER  BY id DESC

and for the "previous row"

SELECT TOP 1 *
FROM   (SELECT TOP 2 *
        FROM   YourTable
        WHERE  id <= @currentId
        ORDER  BY id DESC) T
ORDER  BY id ASC

Upvotes: 2

Related Questions