Next and Previous record - SQL....

L

lidds

Well-known member
Joined
Nov 9, 2004
Messages
210
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
 
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).
 
Back
Top