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]
Go to Top of Page

cidr
Posting Yak Master

207 Posts

Posted - 2013-04-04 : 11:01:02
Spot on, just what I needed.

Thanks:)
Go to Top of Page
   

- Advertisement -