Re: SQL Puzzle: Select a close number.

In <4ovqt6$4l0_at_news2.tds.net> Mike Morgan <mike.morgan_at_teldta.com> writes:
>
>Steve Shiflett <shiflett_steve_at_mm.ssd.lmsc.lockheed.com> wrote:
>
>
>>Here's the deal:
>>
>>create table address(
>>street_number varchar2(6),
>>street_name varchar2(40)
>>);
 

>>insert into this table values:
>>
>>1005 Main St
>>1022 Main St
>>1105 Main St
>>1300 Main St
>>1555 Main St
>>1703 Main St
>>
 

>>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
>
>
>----------------
>
>Steve,
>
>How about this?
>
>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