SQLTeam.com | Weblogs | Forums

Get next record from current record


#1

Take for instance I have a record ID of 9.
On a website, I want to navigate to the next record. Now obviously the website could take the current ID and add 1 to it however that record may not even exist therefore there is nothing to display.

So, what is the best way in SQL to say "here is a record ID, go find me the next one from this ID" and return back that ID?
yes, there is a table with all records that I would be querying from - I just need to know what approach/syntax to use to ask SQL to bring me back the next record up given an ID to it.

Thank you.


#2
select min(id)
  from yourtable
 where id>9

#3

thanks! this worked nicely!


#4

Alternative - I have no idea which performs better though:

SELECT TOP 1 id
FROM   yourtable
WHERE  id > 9
ORDER BY id

Very important that there is an index on [id].

This might be easier to write (than the MIN(id) style above) if your SELECT has lots of columns


#5

Hi

There are new features called LEAD and LAG in Sql Server 2012

LEAD
Accesses data from a "subsequent row" in the same result set without the use of a self-join

LAG
Accesses data from a "previous row" in the same result set without the use of a self-join