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.
Author |
Topic |
cidr
Posting Yak Master
207 Posts |
Posted - 2013-04-04 : 10:07:24
|
Hi and apologies about the bad subject descriptionI 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) t1outer 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:) |
|
|
|
|
|