CODEWITHSUNDEEP
oracle-database
comp_user
comp_user

Reputation: 60

how to select latest record from a created column

I have a table that has a bunch of records that are updated frequently, and I am trying to write a code to pull only the latest record. In an ideal situation, I would use the updated_date column to pull the latest record, however, in many situations, the updated-date column is blank and so, I have to create a new column using the following code:

CASE WHEN UPDATED_DATE IS NULL THEN CREATED_DATE ELSE UPDATED_DATE END latest_date

Basically I made a new column for when updated_date is blank, use the created_date and now, my data looks like this:

updated_date audit_id created_date latest_date
2021-04-02 006 2018-06-06 2021-04-02
NULL 006 2018-06-06 2018-06-06
2020-03-01 006 2018-06-06 2020-03-01
NULL 007 2018-07-07 2018-07-07
2020-04-01 007 2018-07-07 2020-04-01
2019-09-08 007 2018-07-07 2019-09-08

What I would like to retrieve is the latest info only:

updated_date audit_id created_date latest_date
2021-04-02 006 2018-06-06 2021-04-02
2020-04-01 007 2018-07-07 2020-04-01

If someone could please let me know how this data can be retried, that would be great.

thank you

Upvotes: 0

Views: 414

Answers (2)

Justin Cave
Justin Cave

Reputation: 231781

I assume your intention is to get the most recent row for each audit_id. If so, you can do something like

select *
  from (select t.*,
               row_number() over (partition by audit_id
                                      order by coalesce( updated_date, created_date ) desc ) rn
          from your_table t)
 where rn = 1;

If you can have two rows with the same audit_id and latest_date, you could use the rank or dense_rank function rather than row_number to handle ties differently but I'm guessing that is not an issue you'll need to deal with and arbitrarily breaking the tie with row_number is sufficient.

Upvotes: 2

dani herrera
dani herrera

Reputation: 51715

You can use COALESCE and FETCH FIRST n ROWS ONLY (Oracle 12):

SELECT *
FROM table_name
ORDER BY COALESCE(UPDATED_DATE, CREATED_DATE) DESC
FETCH FIRST 2 ROWS ONLY;

Upvotes: 2

Related Questions