asked on

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

Jankovsky

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))
/

jwahl

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
/

sujit_kumar

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.

ASKER CERTIFIED SOLUTION

paquicuba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial