CODEWITHSUNDEEP
sqloracle-databaseoracle10gwindow-functionsanalytic-functions
McArthey
McArthey

Reputation: 1646

Include NULLs in MAX() OVER PARTITION

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   |

Upvotes: 0

Views: 3996

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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

Upvotes: 5

Related Questions