SQL query to get the next step of process if the previous step has 'COMPLETED' using group by clause and joins

Question

I did a lot of effort to retrieve the desired output from tables. But unfortunately, it didn't work out. I have 3 tables process, process_steps, and steps:

Problem Statment: I want to fetch the "next step" for all the processes.

Output should be like this :

id	process_id	next_step	status
1	1	Complete Form	active
2	2	Send the document	active

Answer 1

SELECT distinct on (ps.process_id) p.*,s.name as next_step
FROM public.process_steps ps 
JOIN public.steps s on ps.step_id = s.id
JOIN public.process p on p.id = ps.process_id
WHERE ps.status != 'COMPLETED'
ORDER BY ps.process_id desc, ps.step_id ASC;

I heve tested this query, it is fetching all next step of processes. I hope it would be helpful.