CODEWITHSUNDEEP
sqloracle-databasejpa
robert trudel
robert trudel

Reputation: 5749

Get most recent record, field can be null

I have this table:

prba_id | ba_id | start_date | end_date
      1 | 1     | 2020-01-01 | 2020-02-25
      2 | 1     | 2020-02-25 | null
      3 | 2     | 2020-03-01 | 2020-04-25
      4 | 2     | 2020-04-25 | 2020-05-25

I search to get record for a ba_id the most recent end_date if end_date is null that means directly it is the most recent.

This query seem to work:

select max(coalesce(end_date, to_date('9999-01-01', 'YYYY-MM-DD'))) from prba
where praba.billing_account_id = 2

..but I need all the fields.

I would like to have a JPA solution but if not possible, native is OK.

Upvotes: 1

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

You can use row_number():

select prba.*
from (select prba.*,
             row_number() over (partition by billing_account_id order by end_date desc nulls first) as seqnum
      from t
     ) t
where seqnum = 1;

This provides the information for all billing accounts.

If you want only one you can filter in the subquery - or use:

select prba.*
from prba
where billing_account_id = 2
order by end_date desc nulls first
fetch first 1 row only;

In older versions of Oracle:

select p.*
from (select prba.*
      from prba
      where billing_account_id = 2
      order by end_date desc nulls first
     ) p
where rownum = 1;

Upvotes: 2

Related Questions