CODEWITHSUNDEEP
sqlpostgresql
Horatiu Jeflea
Horatiu Jeflea

Reputation: 7424

SQL Query for Events table

I have an event table in Postgres, with this structure:

event
--------
id: serial, pk
item_id: text, fk
status: text, an enum such as CREATED, IN_PROGRESS, DONE...

This works as a log, so all entries are immutable, change is status will be another INSERT.

I want to efficiently extract all events which have status CREATED, but not DONE.

Upvotes: 0

Views: 468

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I assume that you want ITEMs that meet the conditions. If so:

select item_id
from events
where status in ('CREATED', 'DONE')
group by item_id
having max(status) = 'CREATED';

This uses the fact that 'CREATED' < 'DONE' alphabetically. You can also use conditional aggregation:

having count(*) filter (where status = 'DONE') = 0;

Upvotes: 2

Related Questions