CODEWITHSUNDEEP
sqloracle-databasejoin
nuoritoveri
nuoritoveri

Reputation: 2544

Join history table with itself to get the newest event at specific time

I'm using Oracle 10g.

I want to join history table with itself to get the newest time of an event of specific type for all the events. For example I'm at timestamp 3 and I want to know when was the last timestamp event E occurred.

Here is example history table and desired output:

ID TS EVENT   ID TS OCC
-----------   ---------
A  1          A  1  null    
A  2  E       A  2  2
A  3  E       A  3  3
A  4          A  4  3
A  5          A  5  3
A  6  E       A  6  6
A  7          A  7  6
B  1  E       B  1  1 
B  2          B  2  1
B  3          B  3  1
B  4          B  4  1

Note that 'history' is separate for each entity (marked with ID).

I wanted to do this like this:

select history h1
left outer join
history h2
on h1.ID = h2.ID and h2.event='E' and h2.TS = 
    (
        select 
            max(TS)
        from 
            history h3 
        where 
            h3.task_id = h.task_id and
            h3.created_ts < h.created_ts and
            h3.event = 'E'
    )

but Oracle won't let me, it says:

ORA-01799: a column may not be outer-joined to a subquery

I will appreciate any remarks on this issue.

Upvotes: 0

Views: 146

Answers (1)

krokodilko
krokodilko

Reputation: 36127

Try this:

SELECT t.id,
       t.ts,
       t.event,
       MAX( case event when 'E' then ts end )
        OVER
       ( Partition by id order by ts 
         rows between unbounded preceding and current row ) occ
FROM history t
;

Demo --> http://www.sqlfiddle.com/#!4/50170/2
The query should work on Oracle 10.2 and above.

Upvotes: 4

Related Questions