CODEWITHSUNDEEP
sqloracle-database
Shoner Sul
Shoner Sul

Reputation: 127

selecting top record in ORACLE join statement between subqueries

I would like to ask for your help for constructing a single ORACLE SQL statement:

using job table as below

Object | Operation | Time  
A      | Move      | 12:01  
B      | Move      | 12:02  
C      | Pickup    | 12:03  
D      | Move      | 12:04  
B      | Pickup    | 12:05

to get the result table as below.

Object | Operation | Time  | Cause  
A      | Move      | 12:01 | C  
B      | Move      | 12:02 | C  
D      | Move      | 12:04 | B

This is to figure out which Pickup operation caused each Move operation.
"Cause" column must include Object of the pickup job record with smallest time right next to move operation.

I have some ideas as below but do not know how to.
-. It requires join statement between subquery for Move and subquery for Pickup
-. Subquery for Pickup must be partitioned by move record to be joined
-. Must select top record only from each partition of Pickup subquery

Upvotes: 6

Views: 1416

Answers (3)

knagaev
knagaev

Reputation: 2957

There is one from old-school

select j1.Object, j1.Operation, j1.Time, substr(min(j2.Time || j2.Object), 6) Cause
from job j1, job j2
where j1.Operation like 'Move'
  and j1.Time < j2.Time
  and j2.Operation like 'Pickup'
group by j1.Object, j1.Operation, j1.Time

Here is SQLFiddle

Upvotes: 4

A.B.Cade
A.B.Cade

Reputation: 16905

This is my try:

select object, operation, time, t.pobject
from a join 
   (select object pobject, time cur, lag(time,1, 0) over (order by time  ) prev 
      from a
     where operation = 'Pickup')t 
on a.time > t.prev and a.time <= t.cur
where operation = 'Move';

Here is a sqlfiddle

Upvotes: 4

Florin Ghita
Florin Ghita

Reputation: 17643

This is my try:

select  m.object, m.operation, m.time, 
  max(p.object) keep (dense_rank first order by p.time) cause,
  max(p.time) keep (dense_rank first order by p.time) cause_time
from a m
join a p on (p.time > m.time)
where m.operation = 'Move'
and p.operation = 'Pickup'
group by m.object, m.operation, m.time;

see SQLFiddle

I've put column time as number, this does not have any importance as it is sortable.

I've splitted the table in two, Moves and Pickups and the join is made on time, time of pickup being greater than time of move. (This type of join is not great on performance). Then I choose the pickup with smallest time(first clause, with order by p.time).

Upvotes: 4

Related Questions