Oracle SQL - get the row with either NULL or the max value-CodePudding

I want a where-clause that returns always one row.
If for a query that returns multiple rows one of the values in a specific (nullable DateTime) field is NULL, it should return this row.
If there is no row with a value of NULL, it should return the row with the max-datetime.

For example:

Id	Date
1	2022-01-01
2	NULL
3	2021-01-01

In this example, the row with ID=2 should be returned.

Id	Date
1	2022-01-01
2	2020-01-01
3	2021-01-01

And in this example with no NULL row, the ID=1 row should be returned (because it has the highest date).

How is something like this possible?

Thanks in advance

CodePudding user response：

You want the ID for the highest date, where null is considered higher than any real date. In case of a tie you want the minimum ID. You can use Oracle's KEEP LAST for this:

select min(id) keep (dense_rank last order by date nulls last) from mytable;

CodePudding user response：

I put both options into sample data; they differ on VAR column's value.

SQL> with test (var, id, datum) as
  2    (select 'A', 1, date '2022-01-01' from dual union all
  3     select 'A', 2, NULL              from dual union all
  4     select 'A', 3, date '2021-01-01' from dual union all
  5     --
  6     select 'B', 1, date '2022-01-01' from dual union all
  7     select 'B', 2, date '2020-01-01' from dual union all
  8     select 'B', 3, date '2021-01-01' from dual
  9    ),

CTE ranks rows; they are partitioned by VAR and sorted by datum column's value, sorted in descending order, NULL values first:

 10  temp as
 11    (select var, id, datum,
 12            rank() over (partition by var order by datum desc nulls first) rnk
 13     from test
 14    )

The final query just returns rows that rank as highest:

 15  select var, id, datum
 16  from temp
 17  where rnk = 1;

V         ID DATUM
- ---------- ----------
A          2
B          1 01.01.2022

SQL>

Shortly:

with temp as
  (select var, id, datum,
          rank() over (partition by var order by datum desc nulls first) rnk
   from test
  )
select var, id, datum
from temp
where rnk = 1;

CodePudding user response：

If you have Oracle 12c or up:

Select *
From yourtable
Order by coalesce(date, to_date('9999-12-31', 'yyyy-mm-dd')) desc
Fetch first 1 rows only

Thanks to @littlefoot and @tim_biegeleisen for pointing out that TOP n syntax is not available in Oracle; I learned about FETCH FIRST...!

CodePudding user response：

We can use ROW_NUMBER here with a two-tier sorting logic:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (
                    ORDER BY CASE WHEN Date IS NULL THEN 0 ELSE 1 END,
                    Date DESC) rn
    FROM yourTable t
)

SELECT Id, Date
FROM cte
WHERE rn = 1;

The first sorting level places NULL date records before non NULL date records. The second sorting level, to be used in the event that there are no NULL date records, sorts later date first.