Find out the top three employees in the Department

Staff table:

+----+-------+--------+--------------+
| 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            |
+----+-------+--------+--------------+

Departmental table:

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

The top three salaries in the company mean no more than three salaries are bigger than these values.

select e1.Name as 'Employee', e1.Salary from Employee e1
where 3 >
(
    select count(distinct e2.Salary)
    from Employee e2
    where e2.Salary > e1.Salary
)

Then, we need to connect the table Employee and the table Department to get the Department information.

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )

The results are as follows:

| Department | Employee | Salary |
|------------|----------|--------|
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |

Skill point

Subquery

There are two types of mysql subqueries: nested subqueries and related subqueries

Nested subqueries: execute queries that are not dependent on the external.
(1) when executing a sub query, the result is not displayed, but is passed to an external query for use as a condition of the external query.
(2) execute external query and display the whole result.

Related subquery: depends on external query. In most cases, the table of the external query is referenced in the WHERE clause of the subquery.
(1) take a tuple from the outer query and pass the value of the tuple related column to the inner query.
(2) execute inner query to get the value of sub query operation.
(3) the external query gets the qualified row according to the result or result set returned by the sub query.
(4) then the outer query takes out the next tuple and repeat steps 1-3 until all the outer tuples are processed.

Cartesian product

Two tables join with m x n rows. The current example employee table join will generate 49 rows of data. In the related sub query above, a tuple (a row of data) is taken from the outer query to Cartesian product the table in the sub query.