How to select records based on sequential relationship using SQL query

Question

I am trying to query some information from certain large data on connections among a set of clients and servers. Below are sample data from relevant columns in the table (connection_stats):

+---------------------------------------------------------+
|   timestamp         | client_id | server_id |  status   | 
+---------------------------------------------------------+
| 2013-07-06 10:40:30 |   100     |   800     |  SUCCESS  |
+---------------------------------------------------------+
| 2013-07-06 10:40:50 |   101     |   801     |  FAILED   |
+---------------------------------------------------------+
| 2013-07-06 10:42:00 |   100     |   800     |  ABORTED  |
+---------------------------------------------------------+
| 2013-07-06 10:43:30 |   100     |   801     |  SUCCESS  |
+---------------------------------------------------------+
| 2013-07-06 10:56:00 |   100     |   800     |  FAILED   |
+---------------------------------------------------------+

From this table, I am trying to query all instances of the connection status "ABORTED" immediately followed (in the order of timestamp) by connection status "FAILED", for each client_id, server_id pair. I would like to get both the records - the one with status "ABORTED" and that with status "FAILED". There is one such case in the data sample above - for the pair 100, 800, there is a "FAILED" status immediately after "ABORTED".

I am a novice in SQL and databases and I am completely lost on this one. Any pointers to how to approach this will be much appreciated.

The database is mysql.

Answer 1

Admittedly not very elegant, but what I can come up with straight off the bat that works with MySQL that does not have CTEs or ranking functions, and without a guaranteed unique row id to work with.

SELECT aborted.* FROM Table1 aborted JOIN Table1 failed
  ON aborted.server_id = failed.server_id 
 AND aborted.client_id = failed.client_id
 AND aborted.timestamp < failed.timestamp
LEFT JOIN Table1 filler
  ON filler.server_id = aborted.server_id
 AND filler.client_id = aborted.client_id
 AND aborted.timestamp < filler.timestamp
 AND filler.timestamp < failed.timestamp
WHERE filler.timestamp IS NULL
  AND aborted.status = 'ABORTED' AND failed.status = 'FAILED'
UNION
SELECT failed.* FROM Table1 aborted JOIN Table1 failed
  ON aborted.server_id = failed.server_id
 AND aborted.client_id = failed.client_id
 AND aborted.timestamp < failed.timestamp
LEFT JOIN Table1 filler
  ON filler.server_id = aborted.server_id
 AND filler.client_id = aborted.client_id
 AND aborted.timestamp < filler.timestamp
 AND filler.timestamp < failed.timestamp
WHERE filler.timestamp IS NULL
  AND aborted.status = 'ABORTED' AND failed.status = 'FAILED'

An SQLfiddle to test with.

If you're happy with just one row with both records summarized, you can just select the fields you want from aborted/failed and skip the entire second half of the union (ie the query will be cut in half)

Since I got comments on the UNION, here's the same thing using a JOIN, assuming the timestamp is unique per client/server combination (a unique row id would help here);

SELECT * FROM Table1 t JOIN
(
 SELECT 
   aborted.server_id asid, aborted.client_id acid, aborted.timestamp ats,
    failed.server_id fsid,  failed.client_id fcid,  failed.timestamp fts
 FROM Table1 aborted JOIN Table1 failed
   ON aborted.server_id = failed.server_id
  AND aborted.client_id = failed.client_id
  AND aborted.timestamp < failed.timestamp
 LEFT JOIN Table1 filler
   ON filler.server_id = aborted.server_id
  AND filler.client_id = aborted.client_id
  AND aborted.timestamp < filler.timestamp
  AND filler.timestamp < failed.timestamp
 WHERE filler.timestamp IS NULL
   AND aborted.status = 'ABORTED' AND failed.status = 'FAILED'
) u
WHERE t.server_id=asid AND t.client_id=acid AND t.timestamp=ats
   OR t.server_id=fsid AND t.client_id=fcid AND t.timestamp=fts
ORDER BY timestamp

An SQLfiddle to test with.

Answer 2

I'm answring this question (albeit late) because I want to offer a more general approach. MySQL does not have a lag() or lead() function, but you can implement it using a subquery. The idea is to lookup the next timestamp for the client_id/server_id pair and then join back to the original data to get the full record. This allows you to pull as many records as you want from the "next" record. It also allows you to consider more complicated relationships (say, the "fail" has to be within 3 minutes):

select cs.*, csnext.timestamp as nextTimeStamp, csnext.status as nextStatus
from (select cs.*,
             (select timestamp
              from connection_stats cs2
              where cs2.client_id = cs.client_id and
                    cs2.server_id = cs.server_id and
                    cs2.timestamp > cs.timestamp
              order by cs2.timestamp
              limit 1
             ) as Nexttimestamp
      from connection_stats cs
     ) cs join
     connection_stats csnext
     on csnext.client_id = cs.client_id and
        csnext.server_id = cs.server_id and
        csnext.timestamp = cs.nexttimestamp
where cs.status = 'ABORTED' and
      csnext.status = 'FAILED'

The performance of such a query is greatly improved by having an index on connection_stats(client_id, server_id, timestamp).