Select statement

Question

Post reply

Select statement

Kushla

SSC Enthusiast

Points: 103
More actions
May 27, 2003 at 2:52 am

#175735

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?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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]

luanjubica SSC-Addicted Points: 408 More actions · Answer 2

is the ID field a number?? I see u using the quotes?!

Luani

Antares686 SSC Guru Points: 125444 More actions · Answer 3

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.

Clive Strong SSChampion Points: 11222 More actions · Answer 4

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

sqlsrvr_dba@hotmail.com

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

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]

Melville SSCommitted Points: 1985 More actions · Answer 6

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

--

Kushla SSC Enthusiast Points: 103 More actions · Answer 7

Thank you Frank. It is a numeric column and the select statement works fine.

Hans Lindgren SSChampion Points: 10494 More actions · Answer 8

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!