SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Retrieve closest value to another table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cidr
Posting Yak Master

United Kingdom
206 Posts

Posted - 04/04/2013 :  10:07:24  Show Profile  Reply with Quote
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

Edited by - cidr on 04/04/2013 10:17:12

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 04/04/2013 :  10:33:39  Show Profile  Reply with Quote
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
Go to Top of Page

cidr
Posting Yak Master

United Kingdom
206 Posts

Posted - 04/04/2013 :  11:01:02  Show Profile  Reply with Quote
Spot on, just what I needed.

Thanks:)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000