May 27, 2003 at 2:52 am
I want to return a specified column row with the adjacent number of
rows above and below. e.g.
Select *
From Users
Where ID = '7'
It must return the 2 adjacent top and bottom rows. Is it possible?
May 27, 2003 at 2:57 am
Hi Kushla,
you mean something like
SELECT * FROM Users WHERE id between(7-2) AND (7+2)?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 27, 2003 at 4:05 am
is the ID field a number?? I see u using the quotes?!
Luani
Luani
May 27, 2003 at 4:18 am
Give us an example of the data including things you wouldn't see. From that what you expect to be returned and how you determine that is what you want. That will give us a better idea as to how to help.
May 27, 2003 at 4:24 am
Yeah, an example would help. Franks example would work if the id field is sequential. If you delete a couple rows then the code wouldn't work.
Clive Strong
May 27, 2003 at 4:27 am
Hi Clive,
quote:
Yeah, an example would help. Franks example would work if the id field is sequential. If you delete a couple rows then the code wouldn't work.
the moment I posted it, I was aware of this
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 27, 2003 at 5:11 am
This code will find the previous, current and next values in a run (a run is a sequence with gaps).
If you pick the top or bottom value then only two results will be returned, if you pick a missing value only two results will be returned. If you pick a value outside the range only one value will be returned.
If you want it more generic just say.
--
set nocount on
create table #id (id_no int)
insert into #id values (1)
insert into #id values (3)
insert into #id values (4)
insert into #id values (5)
insert into #id values (6)
insert into #id values (8)
declare @Specified int
declare @Next int
declare @prev int
set @Specified=3
select @Next=(select min(id_no) from #id where id_no > @Specified)
select @prev=(select max(id_no) from #id where id_no < @Specified)
set nocount off
if (@Prev is null) set @prev=0
if (@Next is null) set @Next=(select max(id_no) from #id)
select * from #id where id_no between @prev and @Next
drop table #id
--
May 27, 2003 at 7:18 am
Thank you Frank. It is a numeric column and the select statement works fine.
May 29, 2003 at 7:59 am
Even if the discussion is already concluded, here is another efficient way of solving it:
SELECT * FROM (SELECT TOP 3 * FROM Users WHERE [ID] <= 7 ORDER BY [ID] DESC) AS A
UNION ALL
SELECT * FROM (SELECT TOP 2 * FROM Users WHERE [ID] > 7 ORDER BY [ID] ) AS B
ORDER BY [ID]
Regards, Hans!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply