How to get the previous and next IDs from a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get the previous and next IDs from a table

Author

Topic

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-05-21 : 01:34:53

I have a table that holds images. I want to get the previous image id and the next image id based on the current ID:

ImageID

4
21
56
74
99

So if the current image ID is "21" I'd like to return 4 and 56.

Thanks!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-21 : 01:37:41

[code]
select prev_id = max(ImageID)
from tbl
where ImageID < 21

select next_id = min(ImageID)
from tbl
where ImageID > 21
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-21 : 01:47:22

or in single query


select 	prev_id  = max(case when ImageID < 21 then ImageID end),
	next_id  = min(case when ImageID > 21 then ImageID end)
from	tbl

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-05-21 : 02:38:49

Thank you! That worked great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources