Find closest number using index? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Find closest number using index?

Author

Topic

raeldor
Starting Member

2 Posts

Posted - 2011-07-16 : 01:43:17

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-16 : 11:00:43

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)
)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-16 : 12:50:12

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.

raeldor
Starting Member

2 Posts

Posted - 2011-07-17 : 02:43:44

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)
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-17 : 04:03:01

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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources