Getting the next or previous id

Hi there,
I need a sql query to get the next or previous sequential id where
status >= 1 or status <= 3 given a current id.
current id = 145
note: the gaps between where status is = (1, 2, 3) could be tens,
hundreds, or thousands of record.
example below:
table structure and data:
id status
10 -1
11 -1
12 1
13 ... 144 status are all = -1
145 1
146 ... 250 status are all = -1
251 1
252 ... 300 status are all = -1
1. For the next, the query should return 251
2. For the previous, the query should return 12
Your help and response is greatly appreciated.
Thanks
*** Sent via Developersdex http://www.examnotes.net ***Jun,
For a specific answer, give DDL (table definitions as CREATE TABLE
statements, sample data as INSERT statements, and desired output).
Perhaps you need something like
declare @.current int
set @.current = 145
select min(id) as nextID
from T
where T.id > @.current
and T.status between 1 and 3
select max(id) as previousID
from T
where T.id < @.current
and T.status between 1 and 3
An index on (status, id) or (id, status), depending on
the distribution of status values, may make queries like these
more efficient.
Steve Kass
Drew University
Jun Victorio wrote:

>Hi there,
>I need a sql query to get the next or previous sequential id where
>status >= 1 or status <= 3 given a current id.
>current id = 145
>note: the gaps between where status is = (1, 2, 3) could be tens,
>hundreds, or thousands of record.
>example below:
>table structure and data:
>id status
>10 -1
>11 -1
>12 1
>13 ... 144 status are all = -1
>145 1
>146 ... 250 status are all = -1
>251 1
>252 ... 300 status are all = -1
>1. For the next, the query should return 251
>2. For the previous, the query should return 12
>Your help and response is greatly appreciated.
>Thanks
>
>
>*** Sent via Developersdex http://www.examnotes.net ***
>