So as the title suggests I need to return the records from a table, where these records can belong to a group. If there are several records in a group, return only the last one, and if the record does not belong to any group, return it together.
I have the following tables
(automation_execution) 1 –> n (automation_execution_action) 1 <—> 1 (workable)
I need to return workable table records, where they may or may not be linked to automation tables.
automation_execution
| id | company_id |
|---|---|
| 1 | 1 |
| 2 | 1 |
automation_execution_ations
| id | automation_execution_id | workable_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
workable
| id | company_id | status | created_at |
|---|---|---|---|
| 1 | 1 | finished | 2022-01-19 19:48:24 |
| 2 | 1 | processing | 2022-01-19 18:00:24 |
| 3 | 1 | processing | 2022-01-19 18:00:24 |
| 4 | 1 | processing | 2022-01-19 18:00:24 |
In the example above, we have 4 workables, 1 and 2 belong to an automation and 3 and 4 do not, in this example I would need to return the record 2, 3 and 4.
Advertisement
Answer
So this SQL works:
select workables.*
from (
select workables.*,
automation_execution_actions.automation_execution_id,
row_number()
over (partition by automation_execution_actions.automation_execution_id order by workables.id desc) as rn
from workables
left join automation_execution_actions on automation_execution_actions.workable_id = workables.id
) as workables
where rn = 1
OR automation_execution_id IS NULL
order by id;