Re: [Q]: Weird SQL

From: Youri N. Podchosov <ynp_at_ynp.dialup.access.net>
Date: Fri, 10 Mar 1995 05:25:23 EST
Message-ID: <1995Mar10.052523_at_ynp.dialup.access.net>

In article <3jouol$svr_at_news.starnet.net>, Neil Greene <neil_at_bMD.com> writes:
|> I have the following tables:
|>
|> SOURCE_TABLE
|> position varchar(2),
|> percentage number(7,2)
|>
|> values:
|> 1, 102
|> 1, 110
|> 1, 90
|>
|> DEST_TABLE
|> postion varchar(2),
|> percentage number(7,2),
|> dollar_amt number(7,2),
|>
|> values:
|> 1, 100, 1000
|> 1, 101, 1200
|> 1, 102, 1400
|> 1, 103, 1500
|> 1, 104, 2000
|>
|> I want to take the value of percentage from the SOURCE table and
|> find the dollar_amt from the DEST_TABLE with the closest
|> percentage, taking the minimum value.
|>
|> 102 should return $1,400 since it is an exact match.
|> 90 should return $1,000 since 90 is not in the table and 100 is
|> the next lowest value.

select S.percentage, D.dollar_amt
from SOURCE_TABLE S, DEST_TABLE D
where S.position = D.position and

       D.percentage - S.percentage = (
            select min(dd.percentage - ss.percentage)
            from   SOURCE_TABLE ss, DEST_TABLE dd
            where  ss.percentage = S.percentage and
                   ss.position = S.position and
                   ss.position = dd.position and
                   dd.percentage >= ss.percentage
       )

+-----------------------------------------------------------------------------+

| Youri N. Podchosov (ynp) *** Davidsohn & Son, Inc. NYC *** 718-234-4140 | | Internet: ynp_at_ynp.dialup.access.net CIS: 72723,2202 AOL: ynp, yourip |

 +-----------------------------------------------------------------------------+

Received on Fri Mar 10 1995 - 11:25:23 CET