Write a query to get 2nd maximum salary in an employee table ?

Categories >> Software >> Databases >> SQL PLSQL
Suggest New Category

Question Posted / wgk

69 Answers
118143 Views
Accenture, BirlaSoft, Letse, Logica CMG, Qwest, Rheal Software, Saagam, Semantic Space, Tailor Solution, TCS, TinyERP, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Write a query to get 2nd maximum salary in an employee table ?..

Answer / niladri saha(oracle apps consu

SELECT EMP_NAME EmployeeName, SAL Salary
FROM EMP
WHERE SAL =
( SELECT SAL
FROM
(
SELECT
DISTINCT SAL
FROM EMP
ORDER BY SAL DESC
)
WHERE ROWNUM=2
);

This Query will list all the employees, having second
highest salary.

Is This Answer Correct ?

0 Yes

0 No

Write a query to get 2nd maximum salary in an employee table ?..

Answer / rajesh

Guess this works...

select max(sal)
from (select * from emp
where sal not in (select max(sal) from emp))

Is This Answer Correct ?

0 Yes

0 No

Write a query to get 2nd maximum salary in an employee table ?..

Answer / paddu

Mr. Ebnezer this is not a comedy site, to do the comedy.You
know the answer, post it.otherwise leave it.

Is This Answer Correct ?

0 Yes

0 No

Write a query to get 2nd maximum salary in an employee table ?..

Answer / mark berlin

-- Solution #3
select distinct salary from (
select salary,
RANK() OVER (order by salary desc NUlls last) as RRANK
FROM employees
)
Where RRANK = 2;

Is This Answer Correct ?

0 Yes

0 No

Write a query to get 2nd maximum salary in an employee table ?..

Answer / sagar

Select Min(Salary) from Curtest where Salary
in (select Top 2 Salary from Curtest order by salary desc)

Is This Answer Correct ?

0 Yes

0 No

Write a query to get 2nd maximum salary in an employee table ?..

Answer / prasanna

select * from emp where 2(select count(distinct sal) from
emp e where sal>=e.sal);

Is This Answer Correct ?

0 Yes

0 No

Write a query to get 2nd maximum salary in an employee table ?..

Answer / karthik ramasamy

Select max(salary) from salarymaster where salary <(select
max(salary) from salarymaster)

OR

SELECT MAX(SALARY) FROM SALARYMASTER WHERE SALARY NOT IN
(SELECT MAX(SALARY) FROM SALARYMASTER)

OR

Select max(salary) from salarymaster where salary<(Select
max(salary) from salarymaster)

Is This Answer Correct ?

0 Yes

0 No

Write a query to get 2nd maximum salary in an employee table ?..

Answer / ahamed

The following solution is for getting 6th highest salary
from Employee table ,
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

or

SELECT MIN(Sal) FROM TableName
WHERE Sal IN
(SELECT TOP 6 Sal FROM TableName ORDER BY Sal DESC)

Reference:

http://blog.sqlauthority.com/2008/04/02/sql-server-find-nth-highest-salary-of-employee-query-to-retrieve-the-nth-maximum-value/

Is This Answer Correct ?

0 Yes

0 No

Write a query to get 2nd maximum salary in an employee table ?..

Answer / basheer

ex:
Raj 200
kamal 300
hajka 500
Suresh 200
so 1st max salary is 500,2nd is 300,3rd is 200
we need 2nd maximum only(i.e 300)
Query is below

SELECT MIN(SALARY) FROM EMPLOYEE WHERE SALARY IN (SELECT
DISTINCT TOP 2 SALARY FROM EMPLOYEE ORDER BY SALARY DESC)

I've checked this query.
it will give 2nd maximum value.
if it is 3rd max salary then use TOP 3 instead of TOP 2
if u need detail explanation:
1)
Qry: SELECT DISTINCT TOP 2 SALARY FROM EMPLOYEE ORDER BY
SALARY DESC
Output:500
300
2)
SELECT MIN(SALARY) FROM EMPLOYEE WHERE SALARY IN (SELECT
DISTINCT TOP 2 SALARY FROM EMPLOYEE ORDER BY SALARY DESC)
ans: it gets minimum salary from subquery( from above 1st ans)

Is This Answer Correct ?

0 Yes

0 No

Write a query to get 2nd maximum salary in an employee table ?..

Answer / reddy

select distinct (a.sal) from emp a where &n = (select count
(distinct(b.sal) from emp bmwhere a.sal<=b.sal)

Is This Answer Correct ?

0 Yes

0 No

Post New Answer