Oracle - Finding Max (date) when column could have null values and NULL is the max date-CodePudding

I have a table with a date column that has date values and nulls too. Nulls represent open-ended date and hence it is the max date. How can I find the row with max date using sql

If Null exists, that record is the max date. If no nulls then the row with the max date needs to be returned

CodePudding user response：

One way is to use an analytic function like row_number in a subquery.

Something like this:

Sample data

create table t (id number, expiry date);

insert into t (id, expiry) values (3, sysdate);
insert into t (id, expiry) values (8, null);

Relevant subquery

select id, expiry, row_number() over (order by expiry desc) as rn
from   t;

        ID EXPIRY                          RN
---------- ----------------------- ----------
         8                                  1
         3 01.03.2022 20:27:45              2

Note that order by ... desc by default includes the option nulls first - so this does exactly what you need.

In the outer query, you just need to select where rn = 1:

select id, expiry
from   (
         select id, expiry, row_number() over (order by expiry desc) as rn
         from   t
       )
where  rn = 1;

        ID EXPIRY                 
---------- -----------------------
         8

I asked for clarification regarding "ties". If they are possible in your data, you must clarify the desired handling. For example, if you must return all rows with the "latest date", then you should use rank() instead of row_number(). For example:

Add one more row to the table

insert into t (id, expiry) values (9, null);

Modified query:

select id, expiry
from   (
         select id, expiry, rank() over (order by expiry desc) as rn
         from   t
       )
where  rn = 1;

        ID EXPIRY                 
---------- -----------------------
         8                        
         9

Note that the Oracle optimizer is smart in such queries - it will not, in fact, perform a full ordering of all rows by date. The plan will include a window sort pushed rank operation, which does just the minimum work needed to find the rows where the rank (or row number) is 1; it doesn't fully order all rows by the ordering column (expiry in my examples).