How can I pull the row from the following data

Question

Using Oracle SQL.

COL1        COL2                COL3    COL4
2017-12-01  2017-12-01 02:00    296.82  161
2017-12-01  2017-12-01 06:00    287.25  158
2017-12-01  2017-12-01 10:00    290.3   157
2017-12-01  2017-12-01 14:00    345.58  157
2017-12-01  2017-12-01 18:00    285.13  156
2017-12-01  2017-12-01 22:00    287.68  158
2017-12-02  2017-12-02 02:00    306.5   164
2017-12-02  2017-12-02 06:00    315.79  166
2017-12-02  2017-12-02 10:00    307.59  164

What I need to do is get the MAX(COL3) from each day but I need the other values out of the row as well....I can group by COL1 but I'm having issues returning the other values COL2 and COL4 from the row as well.

Almost there however this is not working It is saying that the from is in a bad location.....

SELECT *,MAX(VDC) OVER (PARTITION BY DATETHE) from (select 
trunc(x.lvl) "DATETHE",
energy_metrics_pdu.WATTS_READING "PDU",
case energy_metrics_pdu.watts_reading
when 0 then NULL
else round(energy_metrics_vdc.watts_reading/energy_metrics_pdu.WATTS_READING,2)
END "RATIO",
energy_metrics_vdc.watts_reading "VDC"
from (  SELECT to_date('01-DEC-2017','DD-MON-YYYY') + (level/24)-0  lvl
        FROM dual
        CONNECT BY LEVEL <= (5*24)-1 ) x 
        LEFT JOIN energy_metrics_pdu on to_char(x.lvl, 'YYYY-MM-DD HH24') = to_char(energy_metrics_pdu.READING_DATE, 'YYYY-MM-DD HH24') 
        LEFT join energy_metrics_vdc on to_char(x.lvl, 'YYYY-MM-DD HH24')||':00' = to_char(energy_metrics_vdc.READING_DATE, 'YYYY-MM-DD HH24:MI')
        where energy_metrics_pdu.watts_reading IS NOT NULL  order by "DATETHE" ASC
         );

Any suggestions...

Use the [OVER clause](https://stackoverflow.com/questions/1092120/over-clause-in-oracle): `SELECT *, MAX(COL3) OVER (PARTITION BY COL1)` — zambonee, Dec 12 '17 at 19:45
@zambonee .. this is an answer. why don't you post it as such? — Vamsi Prabhala, Dec 12 '17 at 19:47
On a side note: COL1 is redundant and should not be stored. You can always get the value via `TRUNC(COL2)`. But well, maybe it's a computed column? — Thorsten Kettner, Dec 12 '17 at 19:56
This is unfortunately not the answer as it only change the VDC for all the rows to the MAX...It doesn't identify the row with the MAX value in it. — BostonMacOSX, Dec 12 '17 at 20:46

score 1 · Answer 1 · answered Dec 12 '17 at 19:53

You want the rows with the maximum COL3 per COL1. The typical way would be ranking with a window function like RANK and keeping the best ranked rows then:

select col1, col2, col3, col4
from
(
  select col1, col2, col3, col4, rank() over (partition by col1 order by col3 desc) as rn
  from mytable
)
where rn = 1
order by col1;

zambonee · Answer 2 · 2017-12-12T19:53:50.517

0

You can do some complicated joins, or you can simply use the OVER clause like this:

SELECT *, MAX(COL3) OVER (PARTITION BY COL1) FROM [table]

edited Dec 12 '17 at 19:53

answered Dec 12 '17 at 19:49

zambonee

1,599
11
17

where is the from of the query located – BostonMacOSX Dec 12 '17 at 19:50
@BostonMacOSX edited to show you a more complete query instead of the snippet I had before. – zambonee Dec 12 '17 at 19:54
almost there but see the above error in the post. ...saying that the from is in the wrong place. – BostonMacOSX Dec 12 '17 at 20:32
2

This is unfortunately not the answer as it only change the VDC for all the rows to the MAX...It doesn't identify the row with the MAX value in it. – BostonMacOSX Dec 12 '17 at 20:46

Barbaros Özhan · Answer 3 · 2017-12-12T21:28:04.500

0

as @Thorsten Kettner said, col1 is not needed to be stored, by considering this in mind, you can use the following :

with t as
(
 select trunc(col2) col2, max(col3) max_col3 
   from mytable
  group by trunc(col2) 
 )
 (
   select m.* from t inner join mytable m on t.col2 = trunc(m.col2) and t.max_col3 = m.col3  
   )

demo1

demo2

edited Dec 12 '17 at 21:28

answered Dec 12 '17 at 20:37

Barbaros Özhan

59,113
10
31
55

Hello there...does this only work if the MAX(VDC) is unique for the day? – BostonMacOSX Dec 12 '17 at 20:49
@BostonMacOSX look at this new demo then : http://sqlfiddle.com/#!4/9caa2/3 ( if you need col2 & 4, there's no way out of having too many rows in this case ) – Barbaros Özhan Dec 12 '17 at 21:15

How can I pull the row from the following data

3 Answers3