CODEWITHSUNDEEP
sqlvertica
valenzio
valenzio

Reputation: 823

SQL Query for events that happend in a specific order

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

Upvotes: 7

Views: 4759

Answers (5)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

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'

Upvotes: 2

LeeLep
LeeLep

Reputation: 31

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

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

Upvotes: 1

Tedo G.
Tedo G.

Reputation: 1565

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

Upvotes: 0

Joe Taras
Joe Taras

Reputation: 15399

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

Upvotes: 0

Related Questions