Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Nov 2006
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query help - find 10 closest

    Hey.
    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?

  • #2
    Regular Coder
    Join Date
    Nov 2006
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    take out the word IS and substitute variables for xinput and yinput. Also you could use BETWEEN.

  • #4
    Regular Coder
    Join Date
    Nov 2006
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yah I got that to work thanks. Sometimes you just need to ask the question to figure it out on your own >


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •