Next/Prev record

23 Mar 2006 3:53 AM

William Stacey [MVP]

If you have an identity column (i.e. 1-N) and you have 5, what is the most
efficent query to get the next (and prev) row? Gaps could happen so there
may not be a 6, but the next in order may be 8 for example. TIA

--
William Stacey [MVP]

23 Mar 2006 5:59 AM

Remus Rusanu [MSFT]

I usually use these:
Prev:
SELECT TOP 1 key FROM table WHERE key < current ORDER BY key DESC
Next:
SELECT TOP 1 key FROM table WHERE key > current ORDER BY key

--
This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu

SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx

Show quote

"William Stacey [MVP]" <william.sta***@gmail.com> wrote in message
news:uw3Tx1iTGHA.424@TK2MSFTNGP12.phx.gbl...
> If you have an identity column (i.e. 1-N) and you have 5, what is the most
> efficent query to get the next (and prev) row? Gaps could happen so there
> may not be a 6, but the next in order may be 8 for example. TIA
>
> --
> William Stacey [MVP]
>
>
>

23 Mar 2006 6:07 AM

William Stacey [MVP]

Thanks Remus.

--
William Stacey [MVP]

Show quote

"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@microsoft.com.nowhere.moon> wrote
in message news:euthl7jTGHA.2156@tk2msftngp13.phx.gbl...
|I usually use these:
| Prev:
| SELECT TOP 1 key FROM table WHERE key < current ORDER BY key DESC
| Next:
| SELECT TOP 1 key FROM table WHERE key > current ORDER BY key

23 Mar 2006 6:17 AM

Uri Dimant

William

create table William(c1 int NOT NULL primary key)
go

insert into William values (1)
insert into William values (2)
insert into William values (3)
insert into William values (4)
insert into William values (5)
insert into William values (6)

declare @d as int
set @d=4
select top 1 *,(select top 1 * from William where c1 >@d order by c1
asc)as n
from William where c1 <@d order by c1 desc

Show quote

"William Stacey [MVP]" <william.sta***@gmail.com> wrote in message
news:uw3Tx1iTGHA.424@TK2MSFTNGP12.phx.gbl...
> If you have an identity column (i.e. 1-N) and you have 5, what is the most
> efficent query to get the next (and prev) row? Gaps could happen so there
> may not be a 6, but the next in order may be 8 for example. TIA
>
> --
> William Stacey [MVP]
>
>
>