Leetcode Department Top Three salaries

Source: Internet
Author: User

The `Employee` table holds all employees. Every employee has an ID, and there is also a column for the department ID.

`+----+-------+--------+--------------+| Id | Name | Salary | DepartmentID |+----+-------+--------+--------------+| 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 |+----+-------+--------+--------------+ `

The `Department` table holds all departments of the company.

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

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.

`+------------+----------+--------+| Department | Employee | Salary |+------------+----------+--------+| IT         | Max      | 90000  | | IT         | Randy    | 85000 |  | IT         | Joe      | 70000  | | Sales      | Henry    | 80000  | | Sales      | Sam      | 60000  |+------------+----------+--------+`
```Select D1.name as department,        E1.name as employee,        E1.salary as salary from department D1, employee E1 where (

select count (distinct e2.salary) from     Employee E2     where e2.salary > e1.salary and E2.departmentid = E1.depart Mentid) < 3 and d1.id = E1.departmentid;
```

Leetcode Department Top Three salaries

Related Keywords:

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

• Sales Support

1 on 1 presale consultation

• After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

• Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.