Given this schema (in postgresql-9.2):
CREATE TABLE foo (
id serial PRIMARY KEY,
...other columns elided...
);
CREATE TYPE event_type AS ENUM ('start', 'stop');
CREATE TABLE foo_event (
id serial PRIMARY KEY,
foo_id integer NOT NULL REFERENCES foo (id),
timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
type event_type NOT NULL
);
How can I get all "running" foos? That is, foos whose most recent event is a 'start', or, even better, foos who were started at least once, and have no stops after their last start (in case I add more event types in the future).
My best attempt so far is:
SELECT * FROM foo
WHERE id NOT IN
(SELECT foo_id FROM foo_event
WHERE type='stop');
The problem here is, of course, that it'll never return any foos that have ever been stopped.
Using a MAX() aggregate to retrieve the most recent start event per foo_id group, you can do a LEFT JOIN against a subquery which retrieves the most recent (also MAX()) stop event which is greater than the start event in the ON clause. Look for those with NULLs in the stop events subquery, meaning no match.
SELECT
foo.*,
fstart.*
FROM
foo
INNER JOIN (
/* Left side gets most recent start events */
SELECT
foo_id,
MAX(timestamp) AS start_ts
FROM
foo_event
WHERE event_type = 'start'
GROUP BY foo_id
) fstart ON foo.id = fstart.foo_id
/* Right side gets most recent stop events */
LEFT JOIN (
SELECT
foo_id,
MAX(timestamp) AS stop_ts
FROM
foo_event
WHERE event_type = 'stop'
GROUP BY foo_id
/* JOIN rows that have a greater stop time than start time */
) fstop ON fstart.foo_id = fstop.foo_id AND fstop.stop_ts > fstart.start_ts
/* And find rows where there's no matching stop event greater than the start */
WHERE fstop.stop_ts IS NULL
Note, use >= rather than > in the ON clause, if a stop event can end at the same time as its corresponding start event...