CODEWITHSUNDEEP
sqldb2
nishantv
nishantv

Reputation: 781

Forming query in DB2 to fetch row based on the values in one column along with order of another column

I apologize if the title seems absurd and lack information I am trying to explain the situation through following example:

Consider the following table-

ID     Event     Time
---------------------
1      EventA     ta
1      EventB     tx
2      EventB     ty
1      EventC     tb
2      EventC     to

I wish to select the ID such that there is an EventC after(based on Time) any instance of EventB.

I could think of the following query:

 select ID from TabET where
    ((select TIME from TabET where Event = EventC order by TIME desc fetch first row only)
    >
     (select TIME from TabET where Event = EventB order by TIME desc fetch first row only))

I am looking for a better approach and alternative as the table in reality is a very big table and this query is just a subquery inside a big query to satisfy a condition.

Edit

The ID is not unique. The problem is to identify the IDs for whcich there is an EventC after(based on TIME) an EventB

Upvotes: 0

Views: 1931

Answers (1)

user1919238
user1919238

Reputation:

You can use a self join:

select distinct t1.ID 
   from table t1
   join table t2 on 
      t1.ID = t2.ID and
      t1.Event = 'EventB' and
      t2.Event = 'EventC' and
      t2.Time > t1.Time

Another approach:

with latest_times as (
   select id, max(time) as time from table 
       where Event='EventC'
       group by id
)
select t1.ID from table t1
    join latest_times on 
        t1.id = latest_times.id and
        t1.Event = 'EventB' and
        latest_times.time > t1.time

Upvotes: 1

Related Questions