CODEWITHSUNDEEP
sqlpostgresql
Radim
Radim

Reputation: 1503

Selecting only the very last set of rows matching the criteria

Considering following data

test1=# select * from sample order by created_at DESC;
 id | status | service |     created_at
----+--------+---------+---------------------
  8 | OK     |       1 | 2015-09-16 11:54:00
  7 | OK     |       1 | 2015-09-16 11:53:00
  6 | FAIL   |       1 | 2015-09-16 11:52:00
  5 | OK     |       1 | 2015-09-16 11:51:00

How can I select only the rows with ID 7 and 8. Using window functions I can get row numbers partitioned over status, but so far did not figure out the way how to limit the results only to the last rows identifying 'successful period' for given service.

Upvotes: 1

Views: 56

Answers (2)

Patrick
Patrick

Reputation: 32161

You need to find the time of the most recent status = 'FAIL' for each service, then select those records of the same service that are more recent:

SELECT *
FROM sample
LEFT JOIN (
  SELECT service, max(created_at) AS last_fail
  FROM sample
  WHERE status = 'FAIL'
  GROUP BY service) f USING (service)
WHERE created_at > last_fail
   OR last_fail IS NULL;     -- also show services without ever failing

This assumes there are only two status codes. If there are more, add a status = 'OK' filter to the WHERE clause.

Upvotes: 1

Jakub Kania
Jakub Kania

Reputation: 16477

The most simple approach would be this:

   SELECT *
   FROM sample AS s
   LEFT JOIN (SELECT service, max(id)
              FROM sample
              WHERE status = 'FAIL'
              GROUP BY service) AS q
   ON s.id > q.id 
      AND s.service = q.service

Upvotes: 1

Related Questions