Re: SQL Puzzle: Select a close number.

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 Received on Mon Jun 03 1996 - 00:00:00 CEST