Update from select and where in clause (dynamic args)

[formingform]

I try to implement below complex SQL by ECTO syntax, the department ID will be a dynamic args posted by frontend page, and the two subquery has been commented in SQL

Could someone help me?

UPDATE departments d
SET top_salary = e.top_salary
FROM(
	-- sub query2: sum top 3 salary group by each deparment
	SELECT department_id, sum(salary) AS top_salary
	FROM (
	  -- sub query1: query top 3 salary for each deparment
		SELECT department_id, salary, ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) AS row_num  
		FROM employers
		WHERE department_id IN (1, 3)
	) sorted
	WHERE sorted.row_num <= 3
	GROUP BY department_id
) e
WHERE d.id = e.department_id

[KP123]

Have you checked out these three pages in the docs?

• Dynamic Queries
• Ecto.Query.API.fragment/1
• Ecto.Query.subquery/1

[formingform]

Thanks.

[03juan]

@formingform

FROM (
	  -- sub query1: query top 3 salary for each deparment
		SELECT department_id, salary, ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) AS row_num  
		FROM employers
		WHERE department_id IN (1, 3)
	) sorted
	WHERE sorted.row_num <= 3
	GROUP BY department_id

You’ll also want row_number/0 for this