Re: SQL Puzzle: Select a close number.

Since you want to look at the closest fit, you have to go through the whole table. All you can prevent is going through the table twice.
For this query use:
SELECT STREET_NUM, etc
FROM ADDRESS
WHERE

       ROWID  = ( SELECT SUBSTR (MIN(TO_CHAR(ABS(1234-A1.street_num),
                                         '0999999') || 
                                     A1.ROWID
                                    ), 8, 19 )
                  FROM ADDRESS A1
                );
                        

Steve Shiflett (shiflett_steve_at_mm.ssd.lmsc.lockheed.com) wrote:
> 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.
> 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);

--
**************************************************************
*                          Saad Ahmad                        *
*                          Senior Software Engineer          *
*                          McHugh Freeman & Associates, Inc. *
*                          Ph:  (414) 798-7457               *
*                          Fax: (414) 798-8619               *
*                          E-Mail: sahmad_at_mfa.com            *
**************************************************************