85000 | | IT | Randy | 85000 | | IT | Will | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | +------------+----------+--------+

Explanation: In the IT department:

Max earns the highest unique salary
Both Randy and Joe earn the second-highest unique salary
Will earns the third-highest unique salary

In the Sales department:

Henry earns the highest salary
Sam earns the second-highest salary
There is no third-highest salary as there are only two employees

Solution

Method 1 - Using Common Table Expressions

Code

[{"language":"SQL","code":"WITH RankedSalaries AS (\n     SELECT \n         e.departmentId,\n         e.salary,\n         DENSE_RANK() OVER (PARTITION BY e.departmentId ORDER BY e.salary DESC) as salaryRank\n     FROM \n         Employee e\n ),\n TopSalaries AS (\n     SELECT \n         rs.departmentId,\n         rs.salary\n     FROM \n         RankedSalaries rs\n     WHERE \n         rs.salaryRank <= 3\n )\n SELECT DISTINCT \n     e.id, \n     e.name, \n     e.salary, \n     e.departmentId\n FROM \n     Employee e\n JOIN \n     TopSalaries ts\n ON \n     e.departmentId = ts.departmentId \n     AND e.salary = ts.salary\n ORDER BY \n     e.departmentId, e.salary DESC, e.id;","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\">WITH</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> RankedSalaries </span><span style=\"color:#D73A49;--shiki-dark:#F97583\">AS</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> (</span></span>\n<span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">     SELECT</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> </span></span>\n<span class=\"line\"><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">         e</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">.</span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">departmentId</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">,</span></span>\n<span class=\"line\"><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">         e</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">.</span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">salary</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">,</span></span>\n<span class=\"line\"><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">         DENSE_RANK&

Related Tags