Selecting adjacent rows - 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

Transact-SQL (2000)

Selecting adjacent rows

Author

Topic

Grasch
Starting Member

3 Posts

Posted - 2004-05-13 : 11:20:17

I am looking for some help on constructing a select statement to return the pair of rows adjacent to a user provided value. My table contains measures and volumes (representing the contents of a tank of liquid) in a series with gaps in the series. Their is no zero entry in the table to represent an "empty" value. Their is known high limit value such that I can guarantee the value being used in the search will not exceed the high value ...
Given data
1.0 100
2.0 200
3.0 300
4.0 400 and so on

a value of 1.5 needs to return 1.0 100 and 2.0 200 a value of .5 needs to return 0.0 0 and 1.0 100 and so on.

If anyone has some ideas on how to approach this (or can point me to something similar) I would greatly appreciate. What I have tried so far includes constructing pairs of select statements and unioning them together but that does not handle this low range problem 0.0 0 problem. I need pretty good performance on this as well this table is used to perform conversions and is accessed frequently and contains a large number of entries.

Thanks for any help.

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-13 : 12:48:21

How about...


USE Northwind
GO

CREATE TABLE myTable99(Col1 decimal(15,2), Col2 int)
GO

INSERT INTO myTable99(Col1,Col2)
SELECT 1.0, 100 UNION ALL
SELECT 2.0, 200 UNION ALL
SELECT 3.0, 300 UNION ALL
SELECT 4.0, 400
GO

DECLARE @x decimal(15,2)

SELECT @x = 1.5 

SELECT Col2 
  FROM myTable99 
 WHERE Col1 = (SELECT MIN(Col1) FROM myTable99 WHERE Col1 > @x)
UNION ALL
SELECT Col2 
  FROM myTable99 
 WHERE Col1 = (SELECT MAX(Col1) FROM myTable99 WHERE Col1 < @x)

SELECT @x = 2.5 

SELECT Col2 
  FROM myTable99 
 WHERE Col1 = (SELECT MIN(Col1) FROM myTable99 WHERE Col1 > @x)
UNION ALL
SELECT Col2 
  FROM myTable99 
 WHERE Col1 = (SELECT MAX(Col1) FROM myTable99 WHERE Col1 < @x)
GO

DROP TABLE myTable99
GO

Brett

8-)

Grasch
Starting Member

3 Posts

Posted - 2004-05-14 : 00:10:06

Brett,
Thanks for this solution. Looks to me like it will handle most of what I need. Wish there was some easy way to handle the scenario if the value being examined is 0.5 and I would like to return two rows where one of the rows is 0 and the other is 100. I tried a variety of ideas based on what you gave me but cannot figure out how to get it the rest of the way there. Works great for everything else though.

Thanks,

George

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-14 : 09:38:37

I am thinking this can be reduced a bit,

create table t ( a decimal(10,1) primary key, b int)

insert into t
select 1.0, 100 union all
select 2.0, 200 union all
select 3.0, 300 union all
select 4.0, 400

declare @val decimal(10,1)
set @val = 0.5

select a,b
from
(
select top 1 a,b
from
(
	select a,b 
	from t 
	union
	select 0 a, 0 b
) d
where a >= @val 
order by a
) dl

union all

select a,b
from
(
select top 1 a,b
from
(
	select a,b 
	from t 
	union
	select 0 a, 0 b
) d
where a <= @val 
order by a desc
) dl

order by a

drop table t

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-05-14 : 13:44:45

Building onto Brett's solution, could you use:

CREATE TABLE myTable99(Col1 decimal(15,2), Col2 int)
GO

INSERT INTO myTable99(Col1,Col2)
SELECT 1.0, 100 UNION ALL
SELECT 2.0, 200 UNION ALL
SELECT 3.0, 300 UNION ALL
SELECT 4.0, 400
GO

DECLARE @x decimal(15,2)

SELECT @x = 0.5 

SELECT Col2 
  FROM myTable99 
 WHERE Col1 = (SELECT MIN(Col1) FROM myTable99 WHERE Col1 > @x)
UNION ALL
SELECT Col2 
  FROM myTable99 
 WHERE Col1 = (SELECT MAX(Col1) FROM myTable99 WHERE Col1 < @x)
UNION ALL SELECT 0
   WHERE NOT EXISTS (SELECT * FROM myTable99 WHERE Col1 < @x)

SELECT @x = 1.5 

SELECT Col2 
  FROM myTable99 
 WHERE Col1 = (SELECT MIN(Col1) FROM myTable99 WHERE Col1 > @x)
UNION ALL
SELECT Col2 
  FROM myTable99 
 WHERE Col1 = (SELECT MAX(Col1) FROM myTable99 WHERE Col1 < @x)
UNION ALL SELECT 0
   WHERE NOT EXISTS (SELECT * FROM myTable99 WHERE Col1 < @x)

SELECT @x = 2.5 

SELECT Col2 
  FROM myTable99 
 WHERE Col1 = (SELECT MIN(Col1) FROM myTable99 WHERE Col1 > @x)
UNION ALL
SELECT Col2 
  FROM myTable99 
 WHERE Col1 = (SELECT MAX(Col1) FROM myTable99 WHERE Col1 < @x)
UNION ALL SELECT 0
   WHERE NOT EXISTS (SELECT * FROM myTable99 WHERE Col1 < @x)

GO

DROP TABLE myTable99
GO

-PatP

Grasch
Starting Member

3 Posts

Posted - 2004-05-14 : 17:52:52

Thanks to all. Both solutions work beautifully. I am always amazed how having someone elses head in on something makes such a big difference.

George

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources