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

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 :) — valenzio, Sep 30 '16 at 07:27

Radu Gheorghiu · Accepted Answer · 2016-09-30T08:00:21.450

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'

I am still trying to understand what is happening here, but this seems to work. Thanks a lot. — valenzio, Sep 30 '16 at 08:11

Tim Biegeleisen · Answer 2 · 2016-09-30T06:59:54.993

1

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

edited Sep 30 '16 at 06:59

answered Sep 30 '16 at 06:41

Tim Biegeleisen

502,043
27
286
360

Where do you define that the Object must have Event B? – valenzio Sep 30 '16 at 07:50
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. – Tim Biegeleisen Sep 30 '16 at 07:56
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? – valenzio Sep 30 '16 at 08:18
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. – Tim Biegeleisen Sep 30 '16 at 08:20
Did you bother to even try the demo I setup? – Tim Biegeleisen Sep 30 '16 at 08:23
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 – valenzio Sep 30 '16 at 11:33
@valenzio Fiddle is prone to breaking. Just try the query directly in Vertica. – Tim Biegeleisen Sep 30 '16 at 11:39
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. – valenzio Sep 30 '16 at 11:42
You don't need to mention `B` because not `A` means `B` – Tim Biegeleisen Sep 30 '16 at 11:51
Yes that I understand, but If you have more events like 'A' , 'B' and 'C' than 'not A' means 'B' or 'C' , :) – valenzio Sep 30 '16 at 13:14

score 0 · Answer 3 · answered Sep 30 '16 at 06:43

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)
   )

score 0 · Answer 4 · answered Sep 30 '16 at 06:44

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

LeeLep · Answer 5 · 2016-09-30T07:50:48.307

0

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

edited Sep 30 '16 at 07:50

answered Sep 30 '16 at 07:44

LeeLep

31
2

SQL Query for events that happend in a specific order

5 Answers5

SQLFiddle