Oracle SQL - max() with NULL values
select CONG_MEMBER_ID
, district
, min(start_dt)
, NULLIF(MAX(NVL(end_dt
,TO_DATE('9999-09-09','YYYY-MM-DD')
)
)
,TO_DATE('9999-09-09','YYYY-MM-DD')
)
from CONG_MEMBER_TERM
where CONG_MEMBER_ID = 1716
group by CONG_MEMBER_ID
, district
;
max(end_dt) keep (dense_rank first order by end_dt desc nulls first)
upd:
SQL Fiddle
Oracle 11g R2 Schema Setup:
CREATE TABLE t
(val int, s date, e date)
;
INSERT ALL
INTO t (val, s, e)
VALUES (1, sysdate-3, sysdate-2)
INTO t (val, s, e)
VALUES (1, sysdate-2, sysdate-1)
INTO t (val, s, e)
VALUES (1, sysdate-1, null)
INTO t (val, s, e)
VALUES (2, sysdate-1, sysdate-.5)
INTO t (val, s, e)
VALUES (2, sysdate-.5, sysdate-.25)
SELECT * FROM dual
;
Query 1:
select val, min(s), max(e) keep (dense_rank first order by e desc nulls first)
from t group by val
Results:
| VAL | MIN(S) | MAX(E)KEEP(DENSE_RANKFIRSTORDERBYEDESCNULLSFIRST) |
---------------------------------------------------------------------------------------------
| 1 | November, 13 2012 14:15:46+0000 | (null) |
| 2 | November, 15 2012 14:15:46+0000 | November, 16 2012 08:15:46+0000 |