CODEWITHSUNDEEP
sqloracle-database
Mox_z
Mox_z

Reputation: 520

Finding a better way to get the most recent status

I have this way that I am checking the most recent status by grabbing the related detail record with the max(created_on) date.

It works, but it just seems really clumsy, and I am hoping to find a better way.

select rd.stat_code from rec r, rec_detl rd
where r.id = rd.rec_id
and r.id = 13455478
and rd.id in (
  select id from rec_detl rd1
  where rd1.rec_id = r.id
  and rd1.created_on = (
    select max(created_on) from rec_detl rd2
    where rd2.rec_id = r.id
));

In that example, the rec_detl table has a stat_code column indicating the status. The rec_detl table has a foreign key that points to the rec table, so each row from the rec table corresponds to multiple rec_detl records.

Basically, a new rec_detl is inserted each time the rec record has its status updated.

So my questions are "Is there some better and less clumsy way of achieving the same thing and grabbing the most recent status?" and "other than the fact this way is sort of clumsy and ugly, is there anything wrong with this approach?"

Upvotes: 0

Views: 529

Answers (1)

astentx
astentx

Reputation: 6751

For a single record you may use fetch first 1 rows only addition, avaliable since 12c.

select *
from rec_detl
where rec_id = 13455478
order by created_on desc
fetch first 1 rows only

As long as you do not use any column from rec table, there's no need to join anything.

If you need to have rec columns with rec_detl columns for small amount of rows and with index on rec_detl.rec_id column, this may be adapted to lateral join. Small is because of this query introduces nested loop lookups.

select *
from rec
  left join lateral(
    select *
    from rec_det
    where rec.id = rec_det.rec_id
    order by created_on desc
    fetch first 1 rows only
  ) q
    on 1 = 1

For mass processing it would be better to use keep dense_rank first, because if will use general join (it will be hash for large datasets in general). But it will require to list all the columns explicitly either aggregated or grouped by:

select
  rec.id
  , max(rec.val) as val
  , max(rec_det.status) keep(dense_rank first order by created_on desc) as last_status
from rec
  left join rec_det
    on rec.id = rec_det.rec_id
group by
  rec.id

db<>fiddle here

Upvotes: 2

Related Questions