[1일1쿼리] 현재 재직중인 Technique Leader 의 월급중 가장 높은 사람의 부서는?

## 현재 재직중인 Technique Leader 의 월급중 가장 높은 사람의 부서는?

WITH RankedSalaries AS (
    SELECT t.emp_no, t.title, t.from_date, s.salary, m.dept_no,
        ROW_NUMBER() OVER (ORDER BY s.salary DESC) AS rnk
    FROM  titles AS t
    JOIN salaries AS s ON s.emp_no = t.emp_no
    JOIN dept_emp AS m ON m.emp_no = t.emp_no
    WHERE 
t.title = 'Technique Leader' 
        AND s.to_date = '9999-01-01'
)
SELECT R.emp_no, R.title, R.from_date, R.salary ,d.dept_no, d.dept_name
FROM RankedSalaries  as R
JOIN departments AS d On d.dept_no = R.dept_no
where rnk = 1;

 

 

SELECT 
    t.emp_no, 
    d.dept_name, 
    t.title, 
    t.from_date, 
    s.salary
FROM 
    titles AS t
JOIN 
    salaries AS s 
ON 
    t.emp_no = s.emp_no
JOIN 
    dept_emp AS m 
ON 
    t.emp_no = m.emp_no
JOIN 
    departments AS d 
ON 
    m.dept_no = d.dept_no
WHERE 
    t.title = 'Technique Leader'
    AND s.to_date = '9999-01-01'
    AND m.to_date = '9999-01-01'
ORDER BY 
    s.salary DESC
LIMIT 1;