How to limit the number through join?

Question

SQL

Hello.

There is a sql query sqlfiddle.com/#!9/773c4b/33 how to make each department have only 2 employees?

Answer 1

If MySQL is version 8, then he learned in window functions, so you can do this:

WITH tmp AS (SELECT department.name as department_name, employee.name, ROW_NUMBER() OVER (PARTITION BY department_id) AS rownum
FROM employee LEFT JOIN department
ON employee.department_id = department.id)
SELECT * FROM tmp
WHERE rownum <= 2

Complete example