CODEWITHSUNDEEP
sqloracle-database
zzstar
zzstar

Reputation: 23

Find of Maximum of Date using SQL

I need SQL Query to find out max of done date by Type .If one the Done Date is null for particular Type then all rows will be null otherwise max of done date

enter image description here

Upvotes: 0

Views: 43

Answers (2)

Alex Poole
Alex Poole

Reputation: 191245

Another window-function approach is last_value across all values for the partition, ordered by done_date, with nulls last - so a null will take precedence over any non-null value:

select no, done_date, type,
  last_value(done_date)
    over (
      partition by type
      order by done_date nulls last
      rows between unbounded preceding and unbounded following
    ) as last_date
from your_table;
NO DONE_DATE TYPE LAST_DATE
1 02-JUL-21 SAB1 null
1 03-JUL-21 SAB1 null
1 null SAB1 null
2 06-JUL-21 SAB2 07-JUL-21
2 07-JUL-21 SAB2 07-JUL-21
2 09-JUL-21 SAB3 null
2 null SAB3 null

Or you could use first_value and reverse the order, but that seems less intuitive.

db<>fiddle

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can use window functions:

select t.*,
       (case when count(*) over (partition by type) = count(done_date) over (partition by type)
             then max(done_date) over (partition by type)
        end) as last_date
from t;

The first condition is checking if there are any NULL values in done_date.

Upvotes: 1

Related Questions