Skip to content
Advertisement

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:

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.

Advertisement

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:

Hope this answers your question.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement