employee * Not included in the result as it has fewer than 2 employees

Solution

Method 1 - Window Functions or Grouping

Intuition

We want all employees with the second-highest salary in each department. This can be done by ranking salaries within each department and selecting those with rank 2. In pandas, we can group and filter accordingly.

Approach

For each department, find the unique salaries in descending order.
The second-highest salary is the second value in this sorted list (if it exists).
Select all employees in that department with that salary.
Return results ordered by emp_id ascending.

Code

[{"language":"MySQL","code":"SELECT emp_id, dept\nFROM employees e\nWHERE salary = (\n    SELECT DISTINCT salary FROM employees e2\n    WHERE e2.dept = e.dept\n    ORDER BY salary DESC LIMIT 1 OFFSET 1\n)\nORDER BY emp_id ASC;","lang":"sql","html":"<pre class=\"shiki shiki-themes github-light github-dark\" style=\"background-color:#fff;--shiki-dark-bg:#24292e;color:#24292e;--shiki-dark:#e1e4e8\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">SELECT</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> emp_id, dept</span></span>\n<span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">FROM</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> employees e</span></span>\n<span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">WHERE</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> salary </span><span style=\"color:#D73A49;--shiki-dark:#F97583\">=</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> (</span></span>\n<span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">    SELECT DISTINCT</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> salary </span><span style=\"color:#D73A49;--shiki-dark:#F97583\">FROM</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> employees e2</span></span>\n<span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">    WHERE</span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\"> e2</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">.</span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">dept</span><span style=\"color:#D73A49;--shiki-dark:#F97583\"> =</span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\"> e</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">.</span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">dept</span></span>\n<span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">    ORDER BY</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> salary </span><span style=\"color:#D73A49;--shiki-dark:#F97583\">DESC</span><span style=\"color:#

Related Tags