Select last N rows that match C condition (PostgreSQL)

I have a table such as

i.id	t.status
140	‘DONE’
140	‘RUNNING’
140	‘READY’
137	‘FAILED’
137	‘DONE’
137	‘DONE’
123	‘DONE’
123	‘DONE’

Which is a result of this query:

SELECT i.id, t.status
FROM items AS i
INNER JOIN tasks AS t
ON i.id = t.item_id
WHERE project_id=2046
ORDER BY u.id DESC

I want to somehow get the id 137, because it’s the first id in which all three rows (tasks) are either ‘DONE’ or ‘FAILED’. I don’t know how to continue.

The way I see it is, as the title of the question say, find the first 3 rows that have the same id and in which the status is either ‘DONE’ or ‘FAILED’, and then give me the id.

Answer

If you just want to know the ID’s matching the criteria, it’s quite easy. If you want all data, I suggest using this as a subquery, and adjust your current query to have their IDs in its result (WHERE i.id IN (...)).

So first of all, we want to exclude all non-relevant rows: WHERE t.status = 'DONE' OR t.status = 'FAILED'

After this, we want to count how many rows are still left. For this we need a COUNT(t.status), and we need to specify what to group by: GROUP BY i.id. (NOTE: adding the COUNT to the SELECT part is optional.)

Once we have this, we want to check which IDs meet our >=3 criterium using HAVING COUNT(t.status) >= 3.

Your end query will be:

SELECT i.id
FROM items i
INNER JOIN tasks t
ON i.id = t.item_id
WHERE project_id=2046 AND (t.status = 'DONE' OR t.status = 'FAILED')
GROUP BY i.id
HAVING COUNT(t.status) >= 3;

Hope this answers your question.