CODEWITHSUNDEEP
sqloracle-databasenull
nibeck
nibeck

Reputation: 669

Oracle SQL - max() with NULL values

I have a table that has a series of time based events, each bound with a start and end date. For the most recent (current) event, the end date is NULL. Im trying to collapse the duplicative rows and only show the earliest start date and the latest end date. With the NULL being in the date field, that row is ignored. I can dummy up an end date value with NVL(), but that will cause the front end logic to search for and replace that value.

Is there anyway to get max() function to sort NULL as high?

CREATE TABLE CONG_MEMBER_TERM
(
  CONG_MEMBER_TERM_ID  NUMBER(10)               NOT NULL,
  CHAMBER_CD           VARCHAR2(30 BYTE)        NOT NULL,
  CONG_MEMBER_ID       NUMBER(10)               NOT NULL,
  STATE_CD             CHAR(2 BYTE)             NOT NULL,
  DISTRICT             NUMBER(10),
  START_DT             TIMESTAMP(6) WITH TIME ZONE,
  END_DT               TIMESTAMP(6) WITH TIME ZONE
)

This query works, but drops the row where end date is NULL.

select CONG_MEMBER_ID, 
       district, 
       min(start_dt), 
       max(end_dt)
  from CONG_MEMBER_TERM
 where CONG_MEMBER_ID = 1716
 group by CONG_MEMBER_ID, district;

This query fixes that, but now I have a "dummy" end date value(9/9/9999). Something I would rather not have to code around.

select CONG_MEMBER_ID, 
       district, 
       min(start_dt), 
       max(nvl(end_dt, to_date('9/9/9999', 'mm/dd/yyyy')))
  from CONG_MEMBER_TERM
 where CONG_MEMBER_ID = 1716
 group by CONG_MEMBER_ID, district;

Thanks.

Upvotes: 31

Views: 59368

Answers (2)

Francis de Sain
Francis de Sain

Reputation: 21

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
;

Upvotes: 2

Kirill Leontev
Kirill Leontev

Reputation: 10931

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 |

Upvotes: 31

Related Questions