This is the 24th day of my participation in the August Text Challenge.More challenges in August

❤ ️ the original ❤ ️

The Employee table contains the information of all employees. Each Employee has its corresponding Id, Name, Salary and department number.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+
Copy the code

The Department table contains information for all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+
Copy the code

Write an SQL query to find all the employees in each department who earned the top three salaries. For example, based on the given table above, the query result should return:

+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Randy | 85000 | | IT | Joe | 85000 | | IT | Will | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- +Copy the code

Explanation:

In the IT department, Max earned the highest salary, Randy and Joe both earned the second highest salary, and Will ranked third. The Sales department only has two employees. Henry’s salary is the highest, and Sam’s salary is the second.

⭐️ ⭐️

For clarity, I build the test environment data in the local test environment.

Build test data

--create table
CREATE TABLE employee (ID NUMBER,NAME VARCHAR2(20),salary NUMBER,departmentid NUMBER);
CREATE TABLE department (ID NUMBER,NAME VARCHAR2(20));
--insert data
INSERT INTO employee VALUES (1.'Joe'.85000.1);
INSERT INTO employee VALUES (2.'Henry'.80000.2);
INSERT INTO employee VALUES (3.'Sam'.60000.2);
INSERT INTO employee VALUES (4.'Max'.90000.1);
INSERT INTO employee VALUES (5.'Janet'.69000.1);
INSERT INTO employee VALUES (6.'Randy'.85000.1);
INSERT INTO employee VALUES (7.'Will'.70000.1);

INSERT INTO department VALUES (1.'IT');
INSERT INTO department VALUES (2.'Sales');
commit;
Copy the code

❤️ let’s get started

Dense_rank () : dense_rank() :

For details on how to use this function:Dense_rank () function

DENSE_RANK ( ) OVER([<partition_by_clause> ] < order_by_clause > )
Copy the code

First, we need to associate two tables:

SELECT d.name   dname,
       e.name   AS ename,
       e.salary
  FROM employee   e,
       department d
 WHERE e.departmentid = d.id
Copy the code

Next, we need to use the dense_rank() function to sort and group the result sets:

SELECT d.name dname,
       e.name AS ename,
       e.salary,
       dense_rank(a)over(PARTITION BY e.departmentid ORDER BY e.salary DESC) dr
  FROM employee   e,
       department d
 WHERE e.departmentid = d.id
Copy the code

The above results have basically been solved. Finally, just take the first three results:

The complete code is as follows:

SELECT t.dname  AS "Department",
       t.ename  AS "Employee",
       t.salary AS "Salary"
  FROM (SELECT d.name dname,
               e.name AS ename,
               e.salary,
               dense_rank(a)over(PARTITION BY e.departmentid ORDER BY e.salary DESC) dr
          FROM employee   e,
               department d
         WHERE e.departmentid = d.id) t
 WHERE t.dr < 4;
Copy the code

Go to LeetCode and see the results:

❄️ comes at the end ❄️

The dense_rank() function is very familiar to you.

This is the end of sharing ~

If you think the article is helpful to you, please like it, favorites it, pay attention to it, comment on it, and support it four times with one button. Your support is the biggest motivation for my creation.