CODEWITHSUNDEEP
sqloracle-databaseselectmax
Clem
Clem

Reputation: 231

How to return maxvalue or nullvalue in SQL?

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.

Upvotes: 1

Views: 1241

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

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

Upvotes: 3

Tony Andrews
Tony Andrews

Reputation: 132570

Try:

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

Upvotes: 3

LesterDove
LesterDove

Reputation: 3044

Change the null values to some future value 

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

Upvotes: 6

Related Questions