SQL: Nhân viên lương cao nhất từng phòng ban

🔴 Rất quan trọng · SQL

---

Cách 1: ROW_NUMBER()

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY department_id
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn = 1;

Luôn trả về duy nhất 1 người mỗi phòng ban.

Cách 2: RANK()

WITH ranked AS (
  SELECT *,
    RANK() OVER (
      PARTITION BY department_id
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn = 1;

Cho cùng hạng nếu cùng lương (2 người cùng top 1).

Backup: Subquery

SELECT * FROM employees e1
WHERE salary = (
  SELECT MAX(salary) FROM employees e2
  WHERE e2.department_id = e1.department_id
);

💡 Tip

• ROW_NUMBER(): luôn unique
• RANK(): cho phép tie
• DENSE_RANK(): tie nhưng không skip hạng

Comments