Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: finding a number with smalles difference

Re: finding a number with smalles difference

From: <casey.kirkpatrick_at_gmail.com>
Date: 29 Mar 2005 07:53:37 -0800
Message-ID: <1112111617.155707.182320@l41g2000cwc.googlegroups.com>


Just use a big enough number for 9999999:

SELECT ID, DESC, PRICE,
CASE WHEN
ABS(PRICE-NVL(LEAD(PRICE)OVER(ORDER BY PRICE),9999999)) < ABS(PRICE-NVL(LAG(PRICE)OVER(ORDER BY PRICE),9999999)) THEN LEAD(ID) OVER ( ORDER BY PRICE)
ELSE
LAG(ID) OVER (ORDER BY PRICE)
END AS CLOSEST_ID,
LEAST(
ABS(PRICE-NVL(LEAD(PRICE)OVER(ORDER BY PRICE),9999999)), ABS(PRICE-NVL(LAG(PRICE)OVER(ORDER BY PRICE),9999999))) DIFF FROM CAT dave wrote:
> hi!
> i`m writing on a procedures to rebook hotel-reservations.
> to do that, i have to find a category with the nearest price to the
> existing booking.
>
> e.g.:
> table: cat (id, desc, price)
> 1 "foo" 10
> 2 "bar" 20
> 3 "non" 40
> 4 "dog" 50
> 5 "xes" 50
>
> the existing booking has cat.id 2, that means the category with the
> nearest price would be cat#1. if cat.id was 3, the category with the
> nearest price would be cat#4. case cat.id = 4 result should be cat#5.
> (same price)
>
>
> in other words: the problem is to find a number (higher or lower)
with
> the smalles (or no) diffence to the give number.
>
>
> so is there an operator like "like" for numbers?
> can anybody give me a hint?
> SELECT id FROM cat WHERE...?
>
> thx in advance, dave
Received on Tue Mar 29 2005 - 09:53:37 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US