?
Solved

nearest min and max value

Posted on 2005-04-26
7
Medium Priority
?
669 Views
Last Modified: 2010-08-05
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
0
Comment
Question by:w2x3y5z7
4 Comments
 
LVL 6

Expert Comment

by:Jankovsky
ID: 13864773
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
 
LVL 12

Expert Comment

by:jwahl
ID: 13864840
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
 
LVL 11

Expert Comment

by:sujit_kumar
ID: 13864977
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
 
LVL 23

Accepted Solution

by:
paquicuba earned 200 total points
ID: 13865979
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month9 days, 12 hours left to enroll

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question