Next and Previous record - SQL....

[lidds]

Could someone please help me with a problem I have. Is there a way using SQL that I can select the previous and next record in a table whilst specifying a specific ID value.

 

E.g. If I currently have a record ID of 19 and I want to know the previous and next ID value from ID 19. Obviously I can not just add or minus 1 from 19 as there could be instances where ID 18 or 20 have been deleted, therefore I need the SQL syntax to jump to the next and previous record. If we use an example of the table as shown below:

 

ID firstname lastname

17 simon liddicott

19 john smith

28 tim smith

 

Therefore I would pass record ID value 19 into the SQL statement and as for the previous and next ID value from 19, therefore returning 17 and 28.

 

I hope that made sense??

 

Thanks in advance

 

Simon

[PlausiblyDamp]

You could potentially do this using a cursor, this would allow you to step forwards or backwards through the data in a table.

 

Is there a reason you need to get each record individually though as this is often not the most optimal way to access information a SQL (or any client / server db).