SQL Query for events that happend in a specific order

Question

I have the following table:

+--------+-------+------+--+
| Object | Event | Time |  |
+--------+-------+------+--+
| Obj1   | A     |    1 |  |
| Obj1   | B     |    3 |  |
| Obj2   | A     |    7 |  |
| Obj2   | B     |    4 |  |
+--------+-------+------+--+

My goal is to get all objects that both had the event A & B with the condition that A happened first (in time). So far I only came up with the query to find me all objects that had A & B without including the time:

SELECT DISTINCT Object 
FROM
    (SELECT * 
     FROM
         (SELECT * 
          FROM table
          INNER JOIN 
              (SELECT Object Obj 
               FROM table 
               WHERE event LIKE '%A%' AS temp_table) ON table.Object = temp_table.Obj) AS temp_final 
     WHERE event LIKE '%B%') AS temp2;

So the end result would be that I get a table that includes only:

Obj1

Since this is the only Object that fulfills all criteria.

The time column is a Date stamp in real life, but for simplicity I used integers.

Thanks you for the help

Muzzleloader

I am using Vertica. I am aware there are such functions such as LAG and LEAD, but I wanted to see if there is an universal approach. Plus I still don't 100% how to use LAG and LEAD for my case :) – Muzzleloader

Answer 1

If you are only tracking two events that happened one after the other, than you can solve this with a single JOIN.

This will work regardless of the number of events Obj1 has, as how you mentioned, you are only interested in A and B existing and being one after the other, respectively.

select distinct t1.object
from TABLE t1
    inner join TABLE t2 on t1.object = t2.object
        and t2.time > t1.time
        and t1.event = 'A'
        and t2.event = 'B'

Here is a sample of the result of the code:

declare @tbl table (obj varchar(10), event varchar(1), time int)

insert @tbl values ('Obj1', 'A', 1), ('Obj1', 'B', 3), ('Obj2', 'A', 7), ('Obj2', 'B', 4)

select distinct t1.obj
from @tbl t1
    inner join @tbl t2 on t1.obj = t2.obj
        and t2.time > t1.time
        and t1.event = 'A'
        and t2.event = 'B'

Desalinate

I am still trying to understand what is happening here, but this seems to work. Thanks a lot. – Muzzleloader

Answer 2

Here is a compact solution which should run across most RDBMS. This solution does not assume that there are only two events, and should run for any number of events.

SELECT t1.Object
FROM yourTable t1
INNER JOIN
(
    SELECT Object, MIN(Time) AS Time
    FROM yourTable
    GROUP BY Object
) t2
    ON t1.Object = t2.Object AND
       ((t1.Event = 'A' AND t1.Time = t2.Time) OR
        t1.Event <> 'A')
GROUP BY t1.Object
HAVING COUNT(*) = 2    -- change this count to match # of events

Demo on MySQL:

SQLFiddle

Chrystel

Where do you define that the Object must have Event B? – Muzzleloader

The condition t1.Event <> 'A' will cover the other record being event B. If the COUNT is 2, then it means it has A and B. This will also work in case you have more than two events. – Chrystel

I don't understand, so if I have multiple events (e.g. A, B, C) and I only want the objects that only had events A & B in that order, how would this work? – Muzzleloader

It may not cover all cases. But for example, if you wanted A first, as well as both B and C present, you'd only have to change the count to 3 and my query would work. Anyway, it definitely works for your problem. – Chrystel

Did you bother to even try the demo I setup? – Chrystel

Yes I did, but it crashed on me after I started changing some values. Like when changing the line Obj2 | A | 7 to Obj2 | A | 3 – Muzzleloader

@Muzzleloader Fiddle is prone to breaking. Just try the query directly in Vertica. – Chrystel

I first tried it in vertica, and it did not produce the right result. Now fiddle is working and I also can't get the query to work probaly when making changes to the tabel. I still can't see how it could work without mentioning condition B in the whole querry. – Muzzleloader

You don't need to mention B because not A means B – Chrystel

Yes that I understand, but If you have more events like 'A' , 'B' and 'C' than 'not A' means 'B' or 'C' , :) – Muzzleloader

Answer 3

Try this:

SELECT DISTINCT object
FROM yourtable t
WHERE EXISTS
    (SELECT FROM yourtable t3
    WHERE t3.object = t.object
    AND t3.event = 'A'
    AND EXISTS
        (SELECT 'B'
        FROM yourtbale t4
        WHERE t4.object = t3.object
        AND t4.event = 'B'
        AND t4.time > t3.time)
   )

Mcdowell

Answer 4

If you are using sql-server:

SELECT
      A.[Object]
    , A.[Time]
    , B.[Time]
FROM
    (SELECT 
        Distinct [Object]
    FROM
        [table] AS A
    WHERE
        A.[Event] = 'A'
    ) AS A
        CROSS APPLY
    (SELECT
        TOP 1 *
    FROM
        [table] AS B
    WHERE
        [Event] = 'B'
        AND
        B.[Object] = A.[Object]
        AND
        A.[Time] < B.[Time]) AS B

Concent

Answer 5

For SQL Server:

;with A as
(select Object, MIN(Time) as Time from table where Event='A' group by Object)
, B as
(select Object, MIN(Time) aS Time from table where Event='B' group by Object)
Select A.Object from A inner join B on B.Object=A.Object where A.Time < B.Time

Cockeyed