SQL - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-01 : 23:28:02

sagi writes "Hi
I want to get the row values just above and below the particular row value when it is passed as a parameter

Regards
sagi"

aclarke
Posting Yak Master

133 Posts

Posted - 2002-01-02 : 00:41:55

Do you need it in one resultset? If not, why not just do


select * from theTable where theColumn < @passedValue order by theColumn desc
select * from theTable where theColumn > @passedValue order by theColumn

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-01-02 : 01:11:13

Hi

Or you could UNION some derived tables together



/*
Create Table Ex(
val int)

Insert Into Ex Values (4)
Insert Into Ex Values (7)
Insert Into Ex Values (9)
Insert Into Ex Values (11)
Insert Into Ex Values (12)

*/

Declare @PassedVal int
SET @PassedVal = 9

SELECT (SELECT Top 1 Val FROM Ex WHERE Val < @PassedVal ORDER BY Val DESC) AS E1
UNION
SELECT Val FROM Ex WHERE Val = @PassedVal
UNION
SELECT (SELECT Top 1 Val FROM Ex WHERE Val > @PassedVal ORDER BY Val) AS E2

Damian

aclarke
Posting Yak Master

133 Posts

Posted - 2002-01-02 : 01:28:55

Hmm oh yes. I guess it's bed time here. I'd just tested that before posting but it gave me an error with the UNION. Turns out I'd forgotten the aliases for the select statements, therefore couldn't use "order by" in them. So I said hang it and posted my answer.

Then a minute later I answered someone else's problem by telling them their query was sending an error because they hadn't aliased a subquery!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources