How to return maxvalue or nullvalue in SQL?

Question

Hello,

I'm running requests on an Oracle database. One of my tables contains time slots for Elements and there can be several time slots for the same Element so that the following example is correct :

ID    ELEMENT_ID    BEGIN_DATE    END_DATE
--------------------------------------------
1     1             01/01/2007    01/06/2007
2     1             01/06/2007   

3     2             01/01/2006    01/07/2006
4     2             01/07/2006    31/12/2006

The time slot for which END_DATE is not filled means that it is still running (so, this is the most recent time slot for the Element).

Thus, when I search the most recent END_DATE for each Element, I want to obtain the rows #2 and #4. So, The problem I'm facing is to consider NULL as the highest END_DATE ...

Is it possible to do this in one single SQL request ?

Of course I tried a simple :

SELECT MAX(END_DATE) FROM ...

but it's not enought because of the NULL values.

Thanks in advance.

Answer 1

Change the null values to some future value

SELECT MAX(NVL(END_DATE,SYSDATE + 1) ...

Answer 2

Try:

select element_id, max(coalesce(end_date, date '4000-12-31')) as max_end_date
from ...

Answer 3

Use an ORDER BY with NULLS FIRST, e.g.:

SELECT DISTINCT
       FIRST_VALUE(id)
       OVER (PARTITION BY element_id
             ORDER BY end_date DESC NULLS FIRST) latest_id,
       element_id,
       FIRST_VALUE(begin_date)
       OVER (PARTITION BY element_id
             ORDER BY end_date DESC NULLS FIRST) latest_id,
       FIRST_VALUE(end_date)
       OVER (PARTITION BY element_id
             ORDER BY end_date DESC NULLS FIRST) latest_id
FROM   ...


ID    ELEMENT_ID    BEGIN_DATE    END_DATE
--------------------------------------------
2     1             01/06/2007   
4     2             01/07/2006    31/12/2006