CODEWITHSUNDEEP
sqloracle-database
Martin Ocando
Martin Ocando

Reputation: 924

get last record from 2nd table

I'm somewhat newbie in Oracle SQL, so bear with me.

I have two tables:

Contaiers:
===========
eq_nbr,
size,
date

Gate Transactions (gate_tran):
==================
eq_nbr,
date,
trucker_id

There are many gate transactions per container, so I need the last record for a particular eq_nbr.

I was thinking on something like:

select ct.eq_nbr, ct.size, ct.date,
  (select trucker_id last over (partition by gt.eq_nbr) from gate_tran gt where 
    gt.eq_nbr = ct.eq_nbr)
from
  containers ct

But it doesn't work. Any ideas?

Upvotes: 0

Views: 57

Answers (3)

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23767

select
  eq_nbr, ct.size, ct.date,
  max(gt.date) as last_date,
  min(trucker_id) keep (dense_rank first order by gt.date desc) as last_trucker_id
from
  containers ct
  join gate_trans gt using(eq_nbr) 
group by eq_nbr, ct.size, ct.date

fiddle
thanks to DazzaL for preparing a scheme :-)

Upvotes: 0

William Proulx
William Proulx

Reputation: 65

Hi you could always take the most recent date added.You could do that this way:

select ct.eq_nbr,ct.size,ct.date from gate_tran gt inner join containers ct where gt.eq_nbr = ct.eq_nbr and date=(select max(date) from gate_tran)

Upvotes: 0

DazzaL
DazzaL

Reputation: 21993

select *
  from (select ct.eq_nbr, ct.size, gt.date, gt.trucker_id,
               row_number() over (partition by ct.eq_nbr order by gt.date desc ) rn
          from containers ct
               inner join gate_tran gt on gt.eq_nbr = ct.eq_nbr)
 where rn = 1;

will get the latest date per eq_nbr. eg http://sqlfiddle.com/#!4/5f52f/1

Upvotes: 1

Related Questions