Include NULLs in MAX() OVER PARTITION

Question

I have an Oracle query that I'd like to return a MAX() over a PARTITION while incorporating NULLs.

For instance, if the raw data is:

NAME   |  DATE
-----------------------
ADAM   | 01/21/14 00:00
ADAM   | 
ADAM   | 01/22/14 00:01
ADAM   | 01/23/14 00:02

Using the following query it returns the following:

MAX(date) OVER (PARTITION BY name ORDER BY date)

NAME   |  DATE
-----------------------
ADAM   | 01/21/14 00:00
ADAM   | 01/22/14 00:01
ADAM   | 01/23/14 00:02
ADAM   | 01/23/14 00:02

Is it possible to have it act as if the NULLs were a MAX() value so it would return the following?

NAME   |  DATE
-----------------------
ADAM   | 01/21/14 00:00
ADAM   | 
ADAM   | 
ADAM   |

Any Aggregate function Ignores `NULL`!(Except `COUNT`), So if you want only `NULL`, need to do `NVL()` — Maheswaran Ravisankar, Jan 22 '14 at 17:52
@MaheswaranRavisankar These are analytic functions, not aggregate. — David Aldridge, Jan 22 '14 at 18:06
@DavidAldridge yes, still the way the rows are processed only gives place to `NULL`.. `MAX()` would never consider null... Aint I right? WHen `NULLS FIRST` is given.. `MAX(NULL)` would happen first, and it is obviously NULL not `MAX(NULL,'A')` returned a `NULL`.. — Maheswaran Ravisankar, Jan 22 '14 at 18:10
@McArthey but what logic you have to make `01/22/13 00:00` as first ? — Maheswaran Ravisankar, Jan 22 '14 at 18:29
Yeah, @MaheswaranRavisankar makes a good point. Your last update makes no sense. Why some rows should get to show NULL as result and others no? Don't you want the same result per `PARTITION BY name`? — ypercubeᵀᴹ, Jan 22 '14 at 19:00
Perhaps my example is flawed but what I'd like to see, basically, is that anything after the NULL will be null'd as if it were the MAX. — McArthey, Jan 22 '14 at 19:34
Define ***after***. "After" in what order? SQL tables do not have an inherent order. Rows wil be displayed in arbitrary order - unless you explicitly use `ORDER BY some_expression` — ypercubeᵀᴹ, Jan 22 '14 at 22:49

ypercubeᵀᴹ · Answer 1 · 2014-01-22T18:06:56.063

5

Yes, you can use the analytic function:

FIRST_VALUE(date_col) OVER (PARTITION BY name ORDER BY date_col DESC NULLS FIRST)

Test at SQL-Fiddle

edited Jan 22 '14 at 18:06

answered Jan 22 '14 at 18:01

ypercubeᵀᴹ

I can't guarantee that the NULL will be the FIRST_VALUE. I have modified my example to represent an example where this wouldn't work. – McArthey Jan 22 '14 at 18:18
1

The `FIRST_VALUE()` will use the `ORDER BY date_col DESC **NULLS FIRST**`, not the order which you are displaying your data. – ypercubeᵀᴹ Jan 22 '14 at 18:19

1 Answers1