nearest min and max value

Dear Oracle Expert,

I have a question about how to retrieve data,
1st data is contain of water level

T_WATER_LEVEL

TO_DAY            WTR_LVL_TOP
01-JAN-2005      2500
02-JAN-2005      2300
03-JAN-2005      2700
04-JAN-2005      2400
05-JAN-2005      2000
06-JAN-2005      2500
07-JAN-2005      2600
08-JAN-2005      2900
09-JAN-2005      3000


2nd data is contain of code of water level

T_WTR_LVL_CODE

WTR_LVL LVL_CODE
A      2000
B      2250
C      2500
D      2750
E      3000
F      3250

I want to join both two table so it can shown the water level and the code like

T_RESULT

TO_DAY            WTR_LVL_TOP      MIN_WTR_LVL      MIN_LVL_CODE      MAX_WTR_LVL      MIN_LVL_CODE
01-JAN-2005      2500            2500            C            2750            D
02-JAN-2005      2300            2250            B            2500            C
03-JAN-2005      2700            2500            C            2750            D
04-JAN-2005      2400            2250            B            2500            C
05-JAN-2005      2000            2000            A            2250            B
06-JAN-2005      2500            2500            C            2750            C
07-JAN-2005      2600            2500            C            2750            C
08-JAN-2005      2900            2750            D            3000            E
09-JAN-2005      3000            3000            E            3250            F

How to make join in query to retrieve data for the result like in T_RESULT table

MIN_WTR_LVL --> nearest minimum value from  T_WATER_LEVEL.WTR_LVL_TOP rever to T_WTR_LVL_CODE.WTR_LVL
MAX_WTR_LVL --> nearest maximum value from  T_WATER_LEVEL.WTR_LVL_TOP rever to T_WTR_LVL_CODE.WTR_LVL

ist possible using SQL to get data like on the T_RESULT table???

Thanks so much B4

W2X3Y5Z7
w2x3y5z7Asked:
Who is Participating?
 
paquicubaCommented:
Try this:

SELECT DISTINCT A.TO_DAY, A.WTR_LVL_TOP,
 MAX(B.WTR_LVL) OVER ( PARTITION BY A.WTR_LVL_TOP)   MIN_WTR_LVL,
MAX(B.LVL_CODE) OVER ( PARTITION BY A.WTR_LVL_TOP)  MIN_LVL_CODE,
MIN(C.WTR_LVL) OVER ( PARTITION BY A.WTR_LVL_TOP)   MAX_WTR_LVL,
MIN(C.LVL_CODE) OVER ( PARTITION BY A.WTR_LVL_TOP)   MAX_LVL_CODE
FROM T_WATER_LEVEL A, T_WTR_LVL_CODE B, T_WTR_LVL_CODE C
WHERE A.WTR_LVL_TOP >= B.WTR_LVL
AND A.WTR_LVL_TOP < C.WTR_LVL
ORDER BY 1
/

TO_DAY    WTR_LVL_TOP MIN_WTR_LVL M MAX_WTR_LVL M
--------- ----------- ----------- - ----------- -
01-JAN-05        2500        2500 C        2750 D
02-JAN-05        2300        2250 B        2500 C
03-JAN-05        2700        2500 C        2750 D
04-JAN-05        2400        2250 B        2500 C
05-JAN-05        2000        2000 A        2250 B
06-JAN-05        2500        2500 C        2750 D
07-JAN-05        2600        2500 C        2750 D
08-JAN-05        2900        2750 D        3000 E
09-JAN-05        3000        3000 E        3250 F

9 rows selected.
0
 
JankovskyCommented:
Select t.TO_DAY,t.WTR_LVL_TOP,c.lolvn as  MIN_WTR_LVL,c.locode as MIN_LVL_CODE,c.hilvl as MAX_WTR_LVL,c.hicode as MAX_LVL_CODE
From T_WATER_LEVEL t
join (select
  WTR_LVL as lolvl
  ,lead(WTR_LVL) over(order by WTR_LVL) as hilvl
  ,LVL_CODE as locode
  ,lead(LVL_CODE) over(order by WTR_LVL) as hicode
from T_WTR_LVL_CODE) c
on(t.WTR_LVL_TOP between c.lolvl and NVL(c.hilvl,9999999999))
/

0
 
jwahlCommented:
or try this:

SELECT
    a.to_day,
    a.wtr_lvl_top,
    TO_NUMBER(SUBSTR(MAX(LPAD(b.lvl_code,8) || b.wtr_lvl), 1, 8)) min_wtr_level,
    SUBSTR(MAX(LPAD(b.lvl_code,8) || b.wtr_lvl), 9)               min_lvl_code,
    TO_NUMBER(SUBSTR(MIN(LPAD(c.lvl_code,8) || c.wtr_lvl), 1, 8)) max_wtr_level,
    SUBSTR(MIN(LPAD(c.lvl_code,8) || c.wtr_lvl),9)                max_lvl_code
FROM
    t_water_level a,
    t_wtr_lvl_code b,
    t_wtr_lvl_code c
WHERE
    b.lvl_code <= a.wtr_lvl_top AND
    c.lvl_code >  a.wtr_lvl_top
GROUP BY
    a.to_day,
    a.wtr_lvl_top
/  
0
 
sujit_kumarCommented:
Consider the queries given below,

The Employee with Nearest minimum salary w.r.t 1500 is,
select * from emp where abs(sal-1500) = any(select min(abs(sal-1500)) from emp where sal < 1500);

The Employee with Nearest Maximum salary w.r.t 1500 is,
select * from emp where abs(sal-1500) = any(select min(abs(sal-1500)) from emp where sal > 1500);

So you design your query accordingly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.