Rails 5 – I need to return the first record of a group, but there are records that have no group

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.

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;