how to get second highest sal of emp table

Categories >> Software >> Databases >> Oracle >> Oracle General
Suggest New Category

Question Posted / shivashanker

6 Answers
10231 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

how to get second highest sal of emp table..

Answer / yadunandan

select max(sal) from emp where sal<(select max(sal) from
emp)

Is This Answer Correct ?

5 Yes

2 No

how to get second highest sal of emp table..

Answer / anilchandu

Select * from emp x where 2=(select count(distinct sal)
from emp y where x.sal<=y.sal)

where x,y are alias names.

If you want 3rd heighest salary then just replace 2 by 3

Is This Answer Correct ?

2 Yes

0 No

how to get second highest sal of emp table..

Answer / manjeet

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

Is This Answer Correct ?

1 Yes

0 No

how to get second highest sal of emp table..

Answer / prabhudatta barick

--IN ORDER TO FIND THE NTH HIGHEST SALARY,
--WE JUST FIND THE SALARY THAT HAS EXACTLY N-1 SALARIES
GREATER THAN ITSELF---

--This is correlated subquery--
select empno,
ename,
sal
from scott.emp e
where &n-1=(select count(distinct b.sal) from scott.emp b
where b.sal>e.sal);

To get second highest salary put n value 2.

Is This Answer Correct ?

1 Yes

0 No

how to get second highest sal of emp table..

Answer / rakhi

SELECT *
FROM (
SELECT employee_id, last_name, salary,
RANK() OVER (ORDER BY salary DESC) EMPRANK
FROM employees)
WHERE emprank = 2;

Is This Answer Correct ?

2 Yes

2 No

how to get second highest sal of emp table..

Answer / amit bhagat

select min(a.sal) from (select disctinct d.sal from emp d
order by sal desc) a where rownum<=2

Is This Answer Correct ?

0 Yes

0 No

Post New Answer