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

automation_execution_ations

workable

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.

CodePudding user response：

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;