Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

What will be the SQL query to select the second highest salary from the employee table?

user-image
Question added by Tanmay Karmakar
Date Posted: 2013/07/28
ADIL MUSTAFA
by ADIL MUSTAFA , Senior System Engineer , Infosys Limited

You can use Rank() analytical function to achieve this.

Deleted user
by Deleted user

the2 nd

SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee)

OR

SELECT FROM Employee E1 WHERE (1) = SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2 WHERE E2.Salary > E1.Salary)

the N th

SELECT FROM Employee E1 WHERE (N-1) = SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2 WHERE E2.Salary > E1.Salary)

select  max(salary)

from     employees

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

Angelo Endaya
by Angelo Endaya , Maximo Application Developer , IBM Solutions Delivery Inc.

SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee);

yasser hassan
by yasser hassan , Senior Oracle developer , zakah and Saudi Customs

select level,max(sal) from emp

where level =2

connect by prior (sal) > sal

group by level

Hassan Abdelwahab
by Hassan Abdelwahab , Card Projects Specialist , Abu Dhabi Islamic Bank

select Max(Sal) from emp where Sal < (select Max(Sal) from emp)

Ali Mahdi Jawad
by Ali Mahdi Jawad , Head of Information Technology , Falcon Cement Co

Select salary from

( select salary from employees order by salary desc )

Where rownum=2;

 

Please notice that rownum is pseudo column

Deleted user
by Deleted user

select distinct sal from emp where sal = (select max(e.sal) from emp ewhere sal < (select max(sal) from emp ) ) order  by sal desc

Shabeer Mohamed Muthachodi
by Shabeer Mohamed Muthachodi

select SALARY from EMPLOYEE EMP

where

2=(

select count(distinct SALARY) from EMPLOYEE

where

EMP.SALARY<=SALARY

);

 

This would work. You may give it a try...

Also you may replace2 with any number n to find nth highest salary.

Muhammad Kaleem ullah -
by Muhammad Kaleem ullah - , Assistant Manager IT Development , Lahore Stock Exchange

<p>select E1.* from employees E inner join ( select E.empid,e.Salary, dens_rank over (order by Salary desc) as RNk from employee E ) T on E1.empid = T.empid where T.RNK =2</p>

Hassan Abazid
by Hassan Abazid , IT Application Manager , Coca-Cola Yemen

SELECT * FROM hr.employees WHERE salary = (SELECT salary FROM (SELECT salary, ROW_NUMBER () OVER (ORDER BY salary DESC) r FROM hr.employees GROUP BY salary) WHERE r = :salary_rank)

More Questions Like This