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)
 Find closest number using index?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raeldor
Starting Member

USA
2 Posts

Posted - 07/16/2011 :  01:43:17  Show Profile  Reply with Quote
Hi All,

If I have a numeric column, is it possible to find the row that has the number closest (either above or below) to a fixed number, but making use of an index for speed?

For example, I know I can do something like 'select top 1 * from mytable order by abs(numberfield-12) desc'. but this doesn't make use of the index on numberfield.

By the way, before you ask the number 12 is being replaced in a dynamically built sql statement. :)

Thanks
Ray

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 07/16/2011 :  11:00:43  Show Profile  Reply with Quote
This may not be any more efficient, but it WILL (should) do index seek(s). I am sure some of the experts on this forum will come up with better and faster solutions, and you and I will look at those and say "Now, why didn't I think of that!!"
select top 1 * from myTable
where numberfield = 
(
	select top 1 N2+12
	from 
	(
		select min(numberfield-12) N2 from myTable where numberfield > 12
		union all
		select max(numberfield-12) N2 from myTable where numberfield < 12
	) S
	order by abs(N2)
)

Edited by - sunitabeck on 07/16/2011 11:01:37
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 07/16/2011 :  12:50:12  Show Profile  Visit webfred's Homepage  Reply with Quote
Sunita I think that is a fine solution.
In particular the use of UNION ALL to make sure that an index can be used.
Thumbs up!


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

raeldor
Starting Member

USA
2 Posts

Posted - 07/17/2011 :  02:43:44  Show Profile  Reply with Quote
I like it! I'll give it a shot... thank you very much!

Ray

quote:
Originally posted by sunitabeck

This may not be any more efficient, but it WILL (should) do index seek(s). I am sure some of the experts on this forum will come up with better and faster solutions, and you and I will look at those and say "Now, why didn't I think of that!!"
select top 1 * from myTable
where numberfield = 
(
	select top 1 N2+12
	from 
	(
		select min(numberfield-12) N2 from myTable where numberfield > 12
		union all
		select max(numberfield-12) N2 from myTable where numberfield < 12
	) S
	order by abs(N2)
)


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30117 Posts

Posted - 07/17/2011 :  04:03:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
MAX and MIN need to scan the partial index defined by the where clause.
In this scenario I think a TOP(1) and order by would be faster.

SELECT TOP(1) WITH TIES Col1
FROM (
SELECT TOP(1) Col1 FROM dbo.Table1 WHERE Col1 > @SomeNumber ORDER BY Col1
UNION ALL
SELECT TOP(1) Col1 FROM dbo.Table1 WHERE Col1 < @SomeNumber ORDER BY Col1 DESC
) AS d
ORDER BY ABS(Col1 - @SomeNumber)


N 56°04'39.26"
E 12°55'05.63"
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.06 seconds. Powered By: Snitz Forums 2000