Re: SQL Puzzle: Select a close number.

I love you guys!

Mike's solution works best, because he guessed that this was something more than delivery of the "closest number". Actually, I'm dragging out x and y coordinates that are associated with the closest address so that I can compute distances.

The next task is to speed up the query. I have around 28,000 records. It takes about 30 seconds to "do" this query with my data.

Here's my plan:

1. Convert the street_number column from varchar2 to number so that I can drop out the conversion functions.
2. I don't beleive an index will work because the use of functions cause the index to be ignored. (It seems I remember reading about this feature)

> >
> >select street_num
> >from address
> >where abs(1234 - to_number(street_num))=
> > (select min(abs(1234 - to_number(street_num)))
> > from address);
> >
> >It works for me.
> >
> >Mike Morgan
> >mike.morgan_at_teldta.com
>
> Why not just
>
> select min(abs(1234-to_number(street_num))) from address;
>
Received on Tue Jun 04 1996 - 00:00:00 CEST