Retrieve closest value to another 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

SQL Server 2008 Forums

Transact-SQL (2008)

Retrieve closest value to another table

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2013-04-04 : 10:07:24

Hi and apologies about the bad subject description

I want to find the value from one table that's closest to the value in another table Below is an example


 select * 
 from 
 (
 select value = 16
 )t1 join 
  (
  select value = 0, title = 'Small'
  union all 
  select value = 10, title = 'Big'--16 
   union all 
  select value = 20, title = 'Large'
  )t2
		on t2.value = t1.value

It should return the MIN value from t2 that's closest to t1. In the example above I have 16 in t1 and want it to join with 10 in t2 so that it pulls the Title = big.

If it was 4 in t1, it would pull 0 from t2 and the Title 'Small'

The actual live table that t1 represents can have several rows so I'd want to return a title from t2 for each row depending on the values in t1.

Please can anyone help me?

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-04 : 10:33:39

[code]select * from
(
select value = 16
) t1
outer apply
(
select top (1) t2.title
from
(
select value = 0, title = 'Small'
union all
select value = 10, title = 'Big'--16
union all
select value = 20, title = 'Large'
)t2
where t2.value < t1.value
order by t2.value desc
) b[/code]

cidr
Posting Yak Master

207 Posts

Posted - 2013-04-04 : 11:01:02

Spot on, just what I needed.

Thanks:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources