select d.Name Department, e1.Name 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 );
@cicide It worked fine when pasted verbatim
I know this query works, but I don't quite understand how this query handles the order of the output.. can somebody explain that to me?
Read about correlated sub queries here :http://beginner-sql-tutorial.com/sql-subquery.htm
Now, for each row of the outer query:
OuterDepartmentId, OuterEmployeeSalary is available to the inner query. The inner query will fetch all the salaries that are greater then OuterEmployeeSalary for department matching OuterDepartmentId and return a count of such distinct salaries
This count can be 0,1 or 2
if 0 -> that means there are no salaries greater then the OuterDepartmentSalary in that department. Hence, it is the greatest salary for that department. And outer query will include that OuterDepartmentId, OuterEmployeeSalary in the output.
if 1 -> there is one salary bigger then OuterEmployeeSalary (it is the second largest salary)
similarly for count 2, there are two larger salaries.
Hope this helps.
I wrote the following code:
Select Department.Name as Department, e.Name as Employee, e.Salary from Employee e Inner Join Department on e.DepartmentId = Department.Id where (Select count(Distinct m.Salary) from Employee m where m.DepartmentId = e.DepartmentId and m.Salary > e.Salary) < 3;
How is the order sorted :o ?
@AndroidDeveloperSZP I believe join is by default INNER, no need to put it.
@lleiou left join will cause null DepartmentId, right?
Why should we use select 'distinct' salary?If two men have the same highest salary they both should be chosen.
@lleiou LEFT or RIGHT JOIN could potentially create null values. The solution doesn't assume any kind of referential integrity between tables.
@1565107657 Actually both will be chosen, however it begs the question, 'are we to select the top 3 distinct salaries'? or are we to select the 3 people who have the highest salary? or more in the event of a tie. An example to consider ... if there are two distinct salaries in a department, $100K and $110K, 5 people have the first and 5 people have the second, we will end up showing 10 people if we use the above code.
@cicide me, too, always Internal Error
@rainhacker thanks very much
My answer is likely with baiji, But The SQL may add ORDER like this:
SELECT d.Name AS Department, e.Name as Employee, e.Salary from Employee as e left join Department as d on e.DepartmentId = d.Id where ( select count(DISTINCT e2.Salary) from Employee as e2 where e.DepartmentId = e2.DepartmentId and e2.Salary >= e.Salary ) <= 3 AND d.Id IS NOT NULL ORDER BY d.Id ASC, e.Salary DESC;
That SQL is work rightly;
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.