CODEWITHSUNDEEP
sqloracle10gaggregate-functions
McArthey
McArthey

Reputation: 1646

Retain NULL in MAX() ORDER BY

I have the following query with the resultant output.

select seq_no, timestamp, 
max(timestamp) over (partition by seq_no) as max
from temp

SEQ_NO  TIMESTAMP       MAX
1       6/27/2013 15:42 6/27/2013 15:43
1       6/27/2013 15:43 6/27/2013 15:43
1       6/27/2013 15:43 6/27/2013 15:43
1       6/27/2013 15:43 6/27/2013 15:43
2                       6/27/2013 15:44
2       6/27/2013 15:44 6/27/2013 15:44

In the case of the highlighted value you can see that the calculated MAX() does not retain the NULL value. I believe I've read that by default the MAX() function ignores NULLs, so this makes sense... but... I would like to retain the NULL for the row, meaning that the entries for SEQ_NO=2 would look like the following.

SEQ_NO  TIMESTAMP       MAX
2                       
2       6/27/2013 15:44 6/27/2013 15:44

I've tried playing with the KEEP() function but am not clear I'm on the right path.

Upvotes: 0

Views: 122

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Just use a case statement:

select seq_no, timestamp, 
       (case when timestamp is not null
             then max(timestamp) over (partition by seq_no)
        end) as maxtimestamp
from temp

Upvotes: 2

Related Questions