How to select records based on sequential relationship using SQL query

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

select * from table t1, table t2 where t1.server_id = t2.server_id and t1.status = 'ABORTED' and t2= 'FAILED'

Not quite elegant, but should be working. Based on GROUP_CONCAT():

Demo

SELECT client_id,server_id,GROUP_CONCAT(status) as all_statuses
FROM   statuses
GROUP  BY client_id,server_id
HAVING all_statuses LIKE '%ABORTED,FAILED%'
ORDER  BY timestamp

I don't have a MySQL DB to test with, but you might give something like this a shot. May need to add some group by columns.

SELECT aborted.*, failed.*
FROM connection_stats aborted
INNER JOIN connection_status nexterror ON aborted.client_id = nexterror.client_id AND nexterror.timestamp > aborted.timestamp
INNER JOIN connection_status failed ON aborted.client_id = failed.client_id AND failed.STATUS = 'FAILED' AND failed.timestamp = MIN(nexterror.timestamp)
WHERE aborted.STATUS = 'ABORTED'

You can group the statuses and can match according to the sequence

SELECT client_id,server_id,GROUP_CONCAT(status) as abort_fail
FROM   `table`    
GROUP  BY client_id,server_id
HAVING abort_fail ='ABORTED,FAILED'
ORDER  BY `timestamp` DESC

Now using GROUP_CONCAT keep in mind there is character limit for 1000 characters so you should take care of it

SELECT t0.clientid, t0.serverid
        , t0.logtime AS abort_time
        , t1.logtime AS fail_time
FROM tmp t0
JOIN tmp t1 ON t1.clientid = t0.clientid AND t1.serverid = t0.serverid
        -- t1 after t0
        AND t1.logtime > t0.logtime
WHERE t0. status = 'ABORTED'
AND t1. status = 'FAILED'
        -- no records inbetween 'aborted' and 'failed'
        -- (not even different 'aborted' and 'failed' records)
AND NOT EXISTS (
        SELECT *
        FROM tmp x
        WHERE x.clientid = t0.clientid AND x.serverid = t0.serverid
        AND x.logtime > t0.logtime
        AND x.logtime < t1.logtime
        )
        ;

UPDATE: if you want to retrieve the two records not joined, but as separate records, you could do:

SELECT t0.*
FROM tmp t0
JOIN (
        SELECT t1.clientid, t1.serverid
        , t1.logtime AS abort_time
        , t2.logtime AS fail_time
        FROM tmp t1
        JOIN tmp t2 ON t2.clientid = t1.clientid AND t2.serverid = t1.serverid
                -- t2 after t1
                AND t2.logtime > t1.logtime
        WHERE t1. status = 'ABORTED'
        AND t2. status = 'FAILED'
                -- no records inbetween 'aborted' and 'failed'
                -- (not even different 'aborted' and 'failed' records)
        AND NOT EXISTS (
                SELECT *
                FROM tmp x
                WHERE x.clientid = t1.clientid AND x.serverid = t1.serverid
                AND x.logtime > t1.logtime
                AND x.LOGTIME < t2.logtime
                )
        ) two ON two.clientid = t0.clientid AND two.serverid = t0.serverid
                AND (two.abort_time = t0.logtime OR two.fail_time = t0.logtime)
        ;

, Or the same rewritten as an EXISTS clause, which is sometimes a bit cleaner, since the t1,t2 tables do not leak into the outer query:

SELECT *
FROM tmp t0
WHERE EXISTS (
        SELECT *
        FROM tmp t1
        JOIN tmp t2 ON t2.clientid = t1.clientid AND t2.serverid = t1.serverid
                -- t2 after t1
                AND t2.logtime > t1.logtime
        WHERE t1. status = 'ABORTED'
        AND t2. status = 'FAILED'
        AND t1.clientid = t0.clientid AND t1.serverid = t0.serverid
        AND t1.logtime = t0.logtime OR t2.logtime = t0.logtime
                -- no records inbetween 'aborted' and 'failed'
                -- (not even different 'aborted' and 'failed' records)
        AND NOT EXISTS (
                SELECT *
                FROM tmp x
                WHERE x.clientid = t1.clientid AND x.serverid = t1.serverid
                AND x.logtime > t1.logtime
                AND x.LOGTIME < t2.logtime
                )
                )
        ;