Query: closest to prefered distance

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

General SQL Server Forums

New to SQL Server Programming

Query: closest to prefered distance

Author

Topic

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-07-04 : 00:44:55

If I have a table that includes an integer field for [Distance]
I wish to return rows which are closest to a "prefered distance"

Lets say I choose a prefered distance of 1500, I would like to return 10 rows, where distance is closest to 1500.

Can such a filter be placed on a query?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-07-04 : 00:56:51

select top 10 * from table
order by
abs(distance-@preferedDistance)

You will have to decide what to do with ties (option to use WITH TIES clause or add extra ORDER BY clauses to resolve)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-05 : 02:55:10


SELECT your columns..
FROM (SELECT ROW_NUMBER() OVER (ORDER BY ABS(distance-@preferedDistance)) AS Seq,*
FROM YourTable
)t
WHERE Seq <=10

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-07-05 : 05:56:51

Look at the execution plan but I think this will be less efficient. The sort space required might be significantly larger unless SQL Server is smart enough to work out what is going on and push the predicate into the subequery. I am not at SQL so I can't check.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources