Second Highest Salary


  • 0

    Click here to see the full article post


  • 0
    A

    select max(salary) SecondHighestSalary
    from employee
    where salary < (select max(salary) from employee)


  • 0
    K

    SELECT Salary AS SecondHighestSalary
    FROM Employee
    WHERE Salary < (SELECT max(Salary) FROM Employee)
    UNION ALL
    SELECT NULL
    ORDER BY 1 DESC
    LIMIT 1


  • 1
    R

    SELECT Salary AS SecondHighestSalary
    FROM Employee
    WHERE Salary < (SELECT max(Salary) FROM Employee)
    UNION ALL
    SELECT NULL
    ORDER BY 1 DESC
    LIMIT 1


  • 0
    C

    SELECT Max(Salary) AS SecondHighestSalary
    FROM Employee
    WHERE Salary NOT IN (SELECT Max(Salary)
    FROM Employee);


  • 0
    J

    typical group-wise question

    select t1.Salary
    from Employee t1
    join Employee t2 on t1.Salary <= t2.Salary
    group by 1
    having count(*) = 2
    

  • 0
    L

    select max(Salary)'SecondHighestSalary' from Employee test1
    where 1=
    (select count(distinct(test2.Salary)) from Employee test2
    where test2.Salary>test1.Salary)


  • 0
    S

    @jingli430
    The return is not null but an empty data set if there is no qualified records in the Employee table, i.e., no record or only one record or all the same Salary records.


  • 0
    S

    This will work too

    select max(salary)
    from employee
    where salary NOT IN (select max(salary)
    from employee
    );


  • 1
    A

    why this doesn't work?
    select Salary as SecondHighestSalary
    from
    (SELECT Salary, row_number() over (order by Salary desc) as rn
    from Employee)
    where rn = 2


  • 0

    Why does this doesn't work?
    It works in my oracle 11g SQL command line but not here:

    SELECT salary "SecondHighestSalary"
    FROM (SELECT salary, ID, RANK() OVER (ORDER BY salary DESC) as rank
    FROM Employee)
    WHERE rank = 2;


  • 0
    4

    @partha.chatterjee786
    rank() over() 如果工资一样 会重复等级排序
    建议使用row_number() over()


  • 0
    R

    select max(e.Salary) as 'SecondHighestSalary' from Employee as e where e.Salary < (select max(salary) from Employee);


  • 0
    H

    select
    (select salary
    from Employee a
    having (select count(*) from Employee b where a.salary < b.salary) = 1
    limit 1
    )
    as secondhighestsalary


  • 0
    L

    wonderful solution.
    my is naive

    select max(Salary) as SecondHighestSalary 
        from Employee 
        where id not in(
            select id 
            from Employee 
            where Salary = (
                select max(Salary) from Employee
            )
    )
    

  • 0
    S

    SELECT a.Salary as SecondHighestSalary
    FROM Employee a
    where (SELECT COUNT(distinct(salary))+1 FROM employee WHERE salary>a.salary)=2


  • 0
    H

    mine is like:
    select min(Salary) as SecondHighestSalary from (select Salary from Employee order by Salary DESC limit 2) as em;


  • 1
    SELECT
      MAX(Salary) AS SecondHighestSalary
    FROM Employee
    WHERE Salary < 
      ( SELECT Max(Salary) FROM Employee);
    

  • 0
    K

    select max(Salary) SecondHighestSalary
    from Employee
    where Salary not in (select max(Salary) from Employee)


  • 0
    P

    SELECT CASE
    WHEN COUNT(*) > 1 AND (MAX(Top2.Salary) != MIN(Top2.Salary))
    THEN MIN(Top2.Salary)
    ELSE NULL
    END as SecondHighestSalary FROM (SELECT Salary from Employee ORDER BY Salary DESC LIMIT 2) AS Top2;

    Personally I felt I used too many functions to solve this, but logically speaking this seems the clearest.


Log in to reply
 

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