Accepted solution without group by or order by


  • 55
    B
    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
                      );

  • 0
    C

    submit this i get internal error?
    anyone submit successfully?


  • 0
    S

    @cicide It worked fine when pasted verbatim


  • 2
    T

    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?


  • 13
    R
    1. Read about correlated sub queries here :http://beginner-sql-tutorial.com/sql-subquery.htm

    2. 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

    3. 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.


  • 1
    P

    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 ?


  • 0
    A

    I think your sql miss "INNER".


  • 0
    M

    @AndroidDeveloperSZP I believe join is by default INNER, no need to put it.


  • 0
    L

    I wanna know why LEFT JOIN here doesn't work?


  • 0
    K

    @rainhacker @baiji
    As much as I understood, every record hits the entire table. Is a correlated sub-query advisable when the tables that one join are huge?


  • 1
    C

    @lleiou left join will cause null DepartmentId, right?


  • 1
    1

    Why should we use select 'distinct' salary?If two men have the same highest salary they both should be chosen.


  • 1
    M

    @lleiou LEFT or RIGHT JOIN could potentially create null values. The solution doesn't assume any kind of referential integrity between tables.


  • 1
    B

    how does the output follows correct order


  • 0
    S

    @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.


  • 0
    L

    @cicide me, too, always Internal Error


  • 0
    R

    @rainhacker thanks very much


  • 0
    U

    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;


  • 0
    J

    I understand the most of it other than why the output comes sorted. Can someone please explain?


  • 0

    This solution involves subquery, not optimal and some company's interviewer don't like subquey
    Check out my solution without any subquery!
    https://discuss.leetcode.com/topic/109295/best-solution-clean-easy-no-subquery


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.