View Full Version : query help - find 10 closest

04-04-2007, 06:08 PM
I have a table with 3 coloumns
IDnum, X and y
x and y are doubles

I need a query that will display the 10 closest cross references of the x and y values with no limiting span from the input x and y.

I want to return the IDnum's for those 10 matches.

That table has around 80,000 records in it, and is expected to see the query run against probably a few dozen times per day. Not heavy use but the performance is a must from this query.

Any ideas where to start?

04-04-2007, 06:21 PM
I think I can get it to work with a span.
eg find all x and y intersections within an x or y span of 20

not exactly sure how to do it in sql cod ebut in pseudo code something like this

Find all idnum where (x is > (xinput - 10) and x < (xinput - 10)) and (y is > (yinput - 10) and x < (yinput - 10))

Any ideas on how to write that query?

04-04-2007, 06:47 PM
take out the word IS and substitute variables for xinput and yinput. Also you could use BETWEEN.

04-04-2007, 08:15 PM
Yah I got that to work thanks. Sometimes you just need to ask the question to figure it out on your own >:)