Re: SQL Puzzle: Select a close number.

In article <31B35ADB.43F6_at_mm.ssd.lmsc.lockheed.com>, Steve Shiflett <shiflett_steve_at_mm.ssd.lmsc.lockheed.com> wrote:
>Here's the deal:
>

 [stuff deleted]
>
>Question:
>
>What is the SQL statement that will return
>the closest street number to street number = '1234'?
>
>You should get 1300.
>
>I can think of some very ugly ways to do this,
>but there *must* be some eloquent way. Does this
>look fun to you? If so, please give me a clue.
>
>
>-----------------
>Steve Shiflett
>shiflett_steve_at_mm.ssd.lmsc.lockheed.com

There really isn't an eloquent way. Depending on the current and future size of the table and nature of the data I might do this one of two ways. If you know you have unique street name and will always be on the same street in the same city etc. (Assuming here we are not using pure SQL) You could take your search number and extend it 50 or 100 units each direction. Then if you retun one tuple you know it is the closest. If you retun more decide which is the closest. If none are retuned you can continue to increment the select until you return tuple(s). Remember to put a limit on this. You would not want an infinate loop.

I actually did this once in an application. The only difference was that given a customers location we had to find the closest installer. We did not use street addresses. We used the area and exchange codes of the phone numbers of each and then converted them to the closest known latitude and longitude (Center of thier city) and calculated the miles between. Not perfect but pretty darn good. The further away we had to search the uglier the query got since we had to know which area codes bordered which. An array was very usefull here since it allowed us to not place agregate functions and calculation in the where clause. We could select those installers in the same area/exchange and surrounding and then make the calculations to miles distant and compare and display. It may seem like a lot of work but the retrieval of multiple tuples for customer choice was important to us.

--
-------------------------------------------------------------------------------
Mark E. Porterfield
porterf_at_primenet.com
The Deloitte & Touche Consulting Group