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

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.

SELECT * from Employee 
WHERE Salary IN (SELECT MAX(Salary) 
                 FROM Employee 
                 WHERE Salary NOT IN (SELECT MAX(Salary) 
                                      FFROM employee));

Try like this..

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.

SELECT lastname, firstname
FROM employees
WHERE salary IN(
    SELECT MAX(salary) 
    FROM employees 
    WHERE salary < (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.

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 < (
        SELECT
            Max(Salary)
        FROM
            Employee
    );

select salary from table order by salary desc limit 1,1

note:this works only for MYSQL

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 < (SELECT max(salary) 
                                FROM employee);

select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );

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

Try this:

SELECT min(salary) 
FROM employee 
WHERE salary IN (SELECT top 2 salary FROM employee ORDER BY salary DESC)

I think this is probably the simplest out of the lot.

SELECT Name FROM Employees group BY Salary DESCENDING limit 2;

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 

SELECT `salary` AS emp_sal, `name` , `id`
FROM `employee`
GROUP BY `salary` ORDER BY `salary` DESC
LIMIT 1 , 1 

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)

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

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);

select max(sal) , Department no. from employee where sal<max(sal)

select max(age) from yd where age<(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 < 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>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 

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
            )
    );

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 <> (Select top 1 b.id from #Employee b ORDER BY b.Salary desc)
order by a.Salary desc

Drop table

drop table #Empoyee

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));

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;

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

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

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 >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 >tout.sal)  as rank  from EMPLOYEE tout
) result
where rank = N;

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 < (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;

select 
    max(salary) 
from 
    emp_demo_table 
where 
    salary < (select max(salary) from emp_demo_table)

Hope this solves the query in simplest of terms.

Thanks

Try this:

 WITH CTE AS
 (
 SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC)RN,* FROM Users
 )
 SELECT * FROM CTE WHERE RN=2

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
)

select MAX(Salary) from Employee WHERE Salary <> (select MAX(Salary) from Employee )

this is the simple query .. if u want the second minimum then just change the max to min and change the less than(<) sign to grater than(>).

    select max(column_name) from table_name where column_name<(select max(column_name) from table_name)

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 <> (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 <> (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<=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;

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 > 0) in place of 2

Example for 5^th highest salary we put n = 5

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);

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.

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

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

Try this to get the respective nth highest salary.

SELECT
    *
FROM
    emp e1
WHERE
    2 = (
        SELECT
            COUNT(salary)
        FROM
            emp e2
        WHERE
            e2.salary >= e1.salary
    )

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 <= 1) loop


cntr := cntr + 1;

fetch c1 into z;

dbms_output.put_line(z.salary);

end loop;

end;

Can we also use

select e2.max(sal), e2.name
from emp e2
where (e2.sal <(Select max (Salary) from empo el))
group by e2.name

Please let me know what is wrong with this approach

SELECT * 
FROM TABLE1 AS A 
WHERE NTH HIGHEST NO.(SELECT COUNT(ATTRIBUTE) FROM TABLE1 AS B) WHERE B.ATTRIBUTE=A.ATTRIBUTE;

SELECT
    salary
FROM
    Employee
ORDER BY
    salary DESC
LIMIT 1,
 1;

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<=: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<=:N )  
 order by sal )
where rownum=1;

To find Second highest salary....

SELECT MAX( salary)  FROM tblEmp WHERE salary< ( 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%...

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;

 - Method 1

    select max(salary) from Employees
        where salary< (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 )

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 < (SELECT Max(e2.salary) FROM emp e2))**

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<=2 order by salary DESC )  where rownum<=1  order by salary ASC ;

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) 

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)

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

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.

try this simple way

select name,salary from employee where salary =
(select max(salary) from employee where salary < (select max(salary) from employee ))

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)

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 >= e1.emp)