CODEWITHSUNDEEP
sql
Marcin K
Marcin K

Reputation: 871

SQL: how to select next id given an id

There is one table T ( id integer, primary key ( id).
I want a parameterized query that, given id i:
will return next consecutive id,
if i = biggest id in T, query should return the smallest id in T (cyclical)

Upvotes: 3

Views: 3278

Answers (2)

Guffa
Guffa

Reputation: 700342

You can select the smallest id over the value @i (if any), and the smallest id, then get the largest:

select max(id)
from (
  select top 1 id
  from T
  where id > @i
  order by id
  union all
  select top 1 id
  from T
  order by id
) x

Or perhaps:

select max(id)
from (
  select min(id) as id
  from T
  where id > @i
  union all
  select min(id)
  from T
) x

Upvotes: 7

Dave
Dave

Reputation: 1234

This appears to be what you're looking for:

CREATE PROCEDURE dbo.ProcName
(
    @ID INTEGER
)
AS
SELECT TOP 1 id
FROM table
WHERE id > @ID
ORDER BY id

Upvotes: 2

Related Questions