How to get second-highest salary employees in a table

Question

It s a question I got this afternoon   There a table contains ID  Name  and Salary of Employees  get names of the second-highest salary employees  in SQL Server  Here s my answer  I just wrote it in paper and not sure that it s perfectly valid  but it seems to work    SELECT Name FROM Employees WHERE Salary      SELECT DISTINCT TOP  1  Salary FROM Employees WHERE Salary NOT IN   SELECT DISTINCT TOP  1  Salary FROM Employees ORDER BY Salary DESCENDING  ORDER BY Salary DESCENDING    I think it s ugly  but it s the only solution come to my mind   Can you suggest me a better query   Thank you very much

User · Answer

SELECT   from Employee  WHERE Salary IN  SELECT MAX Salary                    FROM Employee                   WHERE Salary NOT IN  SELECT MAX Salary                                         FFROM employee      Try like this

User · Answer

Try this one for MSSQL   SELECT     TOP 1 salary FROM               SELECT             TOP 2 salary         FROM             Employees        sal ORDER BY     salary DESC    But you should try this generic SQL query which is working for all kinds of database   SELECT     MAX salary  FROM     Employee WHERE     Salary NOT IN           SELECT             Max Salary          FROM             Employee          OR  SELECT     MAX Salary  FROM     Employee WHERE     Salary  lt            SELECT             Max Salary          FROM             Employee

User · Answer

Try this   SELECT   FROM emptable  WHERE empid IN       SELECT sal row number      OVER partition by sal order by sal desc  RN     FROM emptable     WHERE RN 2

User · Answer

Can we also use  select e2 max sal   e2 name from emp e2 where  e2 sal  lt  Select max  Salary  from empo el   group by e2 name   Please let me know what is wrong with this approach

User · Answer

SELECT MIN a sal   FROM dbo demo a  WHERE a sal IN  SELECT DISTINCT TOP 2 a sal                  FROM dbo demo a                  ORDER BY a sal DESC

User · Answer

i have a table like this in this below image and i am going to find the 2nd largest number in  to user  column     Here is Answer     select MAX to user  FROM db masterledger where to user NOT IN  SELECT   MAX to user  FROM db masterledger

User · Answer

SELECT     salary FROM     Employee ORDER BY     salary DESC LIMIT 1   1

User · Answer

this is the simple query    if u want the second minimum then just change the max to min and change the less than  lt   sign to grater than          select max column name  from table name where column name lt  select max column name  from table name

User · Answer

Try this   SELECT min salary   FROM employee  WHERE salary IN  SELECT top 2 salary FROM employee ORDER BY salary DESC

User · Answer

Try this    WITH CTE AS     SELECT DENSE RANK   OVER  ORDER BY SALARY DESC RN   FROM Users     SELECT   FROM CTE WHERE RN 2

User · Answer

This might help you  SELECT        MIN SALARY   FROM        EMP  WHERE        SALARY in  SELECT                        DISTINCT TOP 2 SALARY                   FROM                        EMP                   ORDER BY                        SALARY DESC                     We can find any nth highest salary by putting n  where n  gt  0  in place of 2  Example for 5th highest salary we put n   5

User · Answer

Here I used two queries for the following scenarios which are asked during an interview   First scenario    Find all second highest salary in the table  Second highest salary with more than    one employee       select   from emp where salary    In  select MAX salary  from emp where salary NOT IN  Select MAX salary  from     emp      Second scenario    Find only the second highest salary in the table    select min temp salary  from  select   from emp order by salary desc limit 2     temp

User · Answer

To find Second highest salary      SELECT MAX  salary   FROM tblEmp WHERE salary lt    SELECT MAX  salary  FROM tblEmp     or  SELECT max salary  FROM tblEmp WHERE salary NOT IN  SELECT max salary  FROM tblEmp    where  salary  is column name and tblEmp is table Name    both are working 100

User · Answer

declare  cntr number   0   cursor c1 is  select salary from employees order by salary desc   z c1 rowtype   begin  open c1   fetch c1 into z   while  c1 found  and  cntr  lt   1  loop   cntr    cntr   1   fetch c1 into z   dbms output put line z salary    end loop   end

User · Answer

select max sal    Department no  from employee where sal lt max sal

User · Answer

Creating temporary table      Create Table  Employee  Id int identity 1 1   Name varchar 500   Salary int    Insert data  Insert Into  Employee     Select  Abul   5000 Union ALL      Select  Babul   6000 Union ALL      Select  Kabul   7000 Union ALL      Select  Ibul   8000 Union ALL      Select  Dabul   9000   Query will be   select top 1   from  Employee a Where a id  lt  gt   Select top 1 b id from  Employee b ORDER BY b Salary desc  order by a Salary desc   Drop table   drop table  Empoyee

User · Answer

Below query can be used to find the nth maximum value  just replace 2 from nth number  select   from emp e1 where 2   select count distinct salary   from emp e2    where e2 emp  gt   e1 emp

User · Answer

Try This one      select   from          select name salary ROW NUMBER   over  order by Salary desc  as     rownum from    employee      as t where t rownum 2   http   askme indianyouth info details write-a-sql-query-to-find-the-10th-highest-employee-salary-from-an-employee-table-explain-your-answer-111

User · Answer

SELECT  salary  AS emp sal   name     id  FROM  employee  GROUP BY  salary  ORDER BY  salary  DESC LIMIT 1   1

User · Answer

Another intuitive way is  - Suppose we want to find Nth highest salary then   1  Sort Employee as per descending order of salary   2  Take first N records using rownum  So in this step Nth record here is Nth highest salary  3  Now sort this temporary result in ascending order  Thus Nth highest salary is now first record  4  Get first record from this temporary result    It will be Nth highest salary   select   from    select   from      select   from           select   from emp order by sal desc       where rownum lt   N      order by sal   where rownum 1    In case there are repeating salaries then in innermost query distinct can be used    select   from    select   from      select   from           select distinct sal  from emp order by 1 desc       where rownum lt   N      order by sal   where rownum 1

User · Answer

Most of the answers are valid  You can use offset with sorted salary as below   SELECT NAME FROM EMPLOYEES WHERE SALARY IN       SELECT DISTINCT             SALARY     FROM EMPLOYEES     ORDER BY SALARY DESC     OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY      To find nth highest salary  replace offset 1 by n

User · Answer

All of the following queries work for MySQL   SELECT MAX salary  FROM Employee WHERE Salary NOT IN  SELECT Max Salary  FROM Employee    SELECT MAX Salary  From Employee WHERE Salary  lt   SELECT Max Salary  FROM Employee    SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1   SELECT Salary FROM  SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 2  AS Emp ORDER BY Salary LIMIT 1

User · Answer

- Method 1      select max salary  from Employees         where salary lt   select max salary  from Employees      - Method 2   select MAX salary  from Employees      where salary not in select MAX salary  from Employees      - Method 3  select MAX salary  from Employees      where salary    select MAX salary  from Employees

User · Answer

select MAX Salary  from Employee WHERE Salary  lt  gt   select MAX Salary  from Employee

User · Answer

I want to post here possibly easiest solution  It worked in mysql   Please check at your end too   SELECT name FROM  emp  WHERE salary     SELECT salary FROM emp e ORDER BY salary DESC LIMIT 1 OFFSET 1

User · Answer

I think this is probably the simplest out of the lot   SELECT Name FROM Employees group BY Salary DESCENDING limit 2

User · Answer

Here s a simple approach   select name from employee where salary  select max salary                from select salary from employee                    minus                    select max salary  from employee

User · Answer

Select   from employee where salary    Select max salary  from employee where salary not in Select max salary from employee    Explanation     Query 1   Select max salary  from employee where salary not in Select max salary  from employee  - This query will retrieve second highest salary Query 2    Select   from employee where  salary  Query 1  - This query will retrieve all the records having second highest salary Second highest salary may have multiple records

User · Answer

If you want to display the name of the employee who is getting the second highest salary then use this   SELECT employee name  FROM employee WHERE salary    SELECT max salary                   FROM employee                 WHERE salary  lt   SELECT max salary                                   FROM employee

User · Answer

Try this  select   from      select ROW NUMBER   over  order by  salary  desc  as sno emp name         salary  from  dbo   Emp    t  where t sno  10  with t as select top  1    from           select top  2  emp name salary from    Emp   e    order by  salary desc  t order  by salary asc

User · Answer

select max age  from yd where age lt  select max age  from HK        True two table Highest   SELECT   FROM HK E1 WHERE 1   SELECT COUNT DISTINCT age  FROM HK E2 WHERE E1 age  lt  E2 age      Second Hightest age RT single table   select age from hk e1 where  3-1     select count distinct  e2 age   from yd e2 where e2 age gt e1 age       same True Second Hight age RT two table  select max age  from YD where age not in  select max age  from YD      second hight age in single table

User · Answer

A compilation of four solutions for the problem     First  Solution - Using Sub Query  SELECT MAX salary  from Employee       ---- This query is going to give you max salary   Now use the above query as subquery to get next highest salary like below   SELECT MAX salary  FROM employee WHERE salary  lt  gt   SELECT MAX salary  from Employee  -- This query is going to give you second highest salary   Now if you want to get name of employee s  getting second highest salary  then use above query as sub-query to get it  like below    SELECT name from employee WHERE salary     SELECT MAX salary  FROM employee WHERE salary  lt  gt   SELECT MAX salary  from Employee    -- This query is going to give you second highest salary    Second Solution - Using Derived Table  SELECT TOP 2 DISTINCT salary  FROM employee ORDER BY salary DESC  -- This is going to give you two highest salary  What you are doing here is to order the salaries in descending order and then select the top 2 salaries    Now order the above result set in ascending order by salary  and get TOP 1   SELECT TOP 1 salary FROM  SELECT TOP 2 DISTINCT salary  FROM employee ORDER BY salary DESC  AS tab  ORDER BY salary   Third Solution - Using correlated sub query  SELECT name  salary FROM Employee e WHERE 2  SELECT COUNT DISTINCT salary  FROM Employee p WHERE e salary lt  p salary    Fourth Solution - Using window function   WITH T AS   SELECT    DENSE RANK   OVER  ORDER BY Salary Desc  AS Rnk FROM Employees   SELECT Name FROM T WHERE Rnk 2

User · Answer

Try this to get the respective nth highest salary   SELECT       FROM     emp e1 WHERE     2             SELECT             COUNT salary          FROM             emp e2         WHERE             e2 salary  gt   e1 salary

User · Answer

try this simple way  select name salary from employee where salary    select max salary  from employee where salary  lt   select max salary  from employee

User · Answer

How about a CTE    WITH Salaries AS       SELECT Name  Salary         DENSE RANK   OVER ORDER BY Salary DESC  AS  SalaryRank      FROM          dbo Employees   SELECT Name  Salary FROM Salaries   WHERE SalaryRank   2   DENSE RANK   will give you all the employees who have the second highest salary - no matter how many employees have the  identical  highest salary

User · Answer

Simple way WITHOUT using any special feature specific to Oracle  MySQL etc   Suppose EMPLOYEE table has data as below  Salaries can be repeated     By manual analysis we can decide ranks as follows  -   Same result can be achieved by query  select    from    select tout sal  id   select count     1 from  select distinct sal  distsal from      EMPLOYEE   where  distsal  gt tout sal   as rank  from EMPLOYEE tout   result order by rank     First we find out distinct salaries   Then we find out count of distinct salaries greater than each row   This is nothing but the rank of that id   For highest salary  this count will be zero  So   1  is done to start rank from 1   Now we can get IDs at Nth rank by adding where clause to above query   select    from    select tout sal  id   select count     1 from  select distinct sal  distsal from      EMPLOYEE   where  distsal  gt tout sal   as rank  from EMPLOYEE tout   result where rank   N

User · Answer

select   from emp where salary           select salary from            select ROW NUMBER   over  order by salary  as  rownum             from emp  t -- Order employees according to salary       where rownum   2 -- Get the second highest salary

User · Answer

supose we have a table like  name       salary  A           10  B           30  C           20  D           40   so what we will do is first will arrange in descending order 40 30 20 10      then we will take only first two number    40 30  then we arrange it in ascending order    30 40  then we will take the first number    30  so in mysql     SELECT   FROM  SELECT    FROM employee   order by salary DESC LIMIT 2  order by salary ASC LIMIT 1    in oracle     SELECT   FROM  SELECT    FROM employee  where rownum lt  2 order by salary DESC    where rownum lt  1  order by salary ASC

User · Answer

SELECT   DENSE RANK   OVER  ORDER BY Salary Desc  AS Rnk INTO  tmp1 FROM Employees  SELECT   FROM  tmp1 WHERE Rnk   2 DROP TABLE  tmp1   SELECT Name SALARY FROM  Employees WHERE  Salary    SELECT MIN Salary               FROM    SELECT DISTINCT TOP  2  Salary                      FROM   Employees                      ORDER  BY Salary DESC  T

User · Answer

Using this SQL  Second highest salary will get with Employee Name  Select top 1 start at 2 salary from employee group by salary order by salary desc

User · Answer

SELECT MAX Salary  FROM Employee WHERE Salary NOT IN  SELECT MAX Salary  FROM Employee

User · Answer

SELECT name FROM employee WHERE salary    SELECT MIN salary     FROM  SELECT TOP  2  salary   FROM employee   ORDER BY salary DESC

User · Answer

The simple way is to use OFFSET  Not only second  any position we can query using offset   SELECT SALARY NAME FROM EMPLOYEE ORDER BY SALARY DESC LIMIT 1 OFFSET 1 --Second largest  SELECT SALARY NAME FROM EMPLOYEE ORDER BY SALARY DESC LIMIT 1 OFFSET 9 --For 10th largest

User · Answer

for nth highest salary  its easy way  select t name t sal from  select name sal dense rank   over  order by sal desc  as rank from emp  t where t rank 6    suppose i find 6th highest salary

User · Answer

There are two way to do this first   Use subquery to find the 2nd highest  SELECT MAX salary  FROM employees WHERE salary NOT IN   SELECT MAX  salary  FROM employees    But this solution is not much good as if you need to find out the 10 or 100th highest then you may be in trouble  So instead go for window function like  select   from   select salary ROW NUMBER   over   order by Salary desc  as rownum from    employees   as t where t rownum 2   By using this method you can find out nth highest salary without any trouble

User · Answer

Try this  This will give dynamic results irrespective of no of rows  SELECT   FROM emp WHERE salary    SELECT max e1 salary   FROM emp e1 WHERE e1 salary  lt   SELECT Max e2 salary  FROM emp e2

User · Answer

Try this query for 2ndHighest salary   SELECT MIN salary  AS  2ndHighest    FROM tbl Emp   WHERE salary IN  SELECT TOP 2 salary FROM tbl Emp ORDER BY 1 DESC

User · Answer

I think you would want to use DENSE RANK as you don t know how many employees have the same salary and you did say you wanted nameS of employees   CREATE TABLE  Test       Id INT      Name NVARCHAR 12       Salary MONEY    SELECT x Name  x Salary FROM                   SELECT  Name  Salary  DENSE RANK   OVER  ORDER BY Salary DESC  as Rnk         FROM     Test           x WHERE x Rnk   2   ROW NUMBER would give you unique numbering even if the salaries tied  and plain RANK would not give you a  2  as a rank if you had multiple people tying for highest salary  I ve corrected this as DENSE RANK does the best job for this

User · Answer

This query display all the details of the Employees with second highest salary  SELECT       FROM     Employees WHERE     salary IN           SELECT             max salary          FROM             Employees         WHERE             salary NOT IN                   SELECT                     max salary                  FROM                     Employees

User · Answer

SELECT lastname  firstname FROM employees WHERE salary IN      SELECT MAX salary       FROM employees      WHERE salary  lt   SELECT MAX salary  FROM employees      So here is what the code mentioned above does   It returns the last names followed by the first names of the employees that have a salary that is smaller than the max salary of all the employees but it s also the max salary of the rest employees that don t have the max salary   In other words  It returns the names of the employees that have the second maximum salary

User · Answer

select MAX Salary  from Employee WHERE Salary NOT IN  select MAX Salary  from Employee

User · Answer

To get the names of the employees with the 2nd highest distinct salary amount you can use    WITH T AS   SELECT           DENSE RANK   OVER  ORDER BY Salary Desc  AS Rnk FROM Employees   SELECT Name FROM T WHERE Rnk 2    If Salary is indexed the following may well be more efficient though especially if there are many employees   SELECT Name FROM   Employees WHERE  Salary    SELECT MIN Salary                   FROM    SELECT DISTINCT TOP  2  Salary                          FROM   Employees                          ORDER  BY Salary DESC  T     Test Script  CREATE TABLE Employees          Name   VARCHAR 50        Salary FLOAT      INSERT INTO Employees SELECT TOP 1000000 s1 name                     abs checksum newid     FROM   sysobjects s1         sysobjects s2  CREATE NONCLUSTERED INDEX ix   ON Employees Salary   SELECT Name FROM   Employees WHERE  Salary    SELECT MIN Salary                   FROM    SELECT DISTINCT TOP  2  Salary                          FROM   Employees                          ORDER  BY Salary DESC  T    WITH T      AS  SELECT                    DENSE RANK   OVER  ORDER BY Salary DESC  AS Rnk          FROM   Employees  SELECT Name FROM   T WHERE  Rnk   2   SELECT Name FROM   Employees WHERE  Salary    SELECT DISTINCT TOP  1  Salary                  FROM   Employees                  WHERE  Salary NOT IN  SELECT DISTINCT TOP  1  Salary                                        FROM   Employees                                        ORDER  BY Salary DESC                   ORDER  BY Salary DESC   SELECT Name FROM   Employees WHERE  Salary    SELECT TOP 1 Salary                  FROM    SELECT TOP 2 Salary                          FROM   Employees                          ORDER  BY Salary DESC  sel                  ORDER  BY Salary ASC

User · Answer

SELECT    FROM TABLE1 AS A  WHERE NTH HIGHEST NO  SELECT COUNT ATTRIBUTE  FROM TABLE1 AS B  WHERE B ATTRIBUTE A ATTRIBUTE

User · Answer

select salary from table order by salary desc limit 1 1   note this works only for MYSQL

User · Answer

select      max salary   from      emp demo table  where      salary  lt   select max salary  from emp demo table    Hope this solves the query in simplest of terms   Thanks

[sql] How to get second-highest salary employees in a table

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to optimization