how to get second highest sal of emp table
Answers were Sorted based on User's Feedback
Answer / yadunandan
select max(sal) from emp where sal<(select max(sal) from
emp)
Is This Answer Correct ? | 4 Yes | 2 No |
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 |
Answer / manjeet
select max(sal) from emp where sal not in (select max(sal)
from emp)
Is This Answer Correct ? | 1 Yes | 0 No |
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 |
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 |
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 |
How many types of table in Oracle?
Can we connect to ORACLE db using Windows Authentication?
What is Hash Cluster ?
how to get last monday of a month?
How to join two tables in a single query using oracle?
where we have to use ON clause and where we have to use USING clause in JOINS
How can we Update a table with out using UPDATE command?
What is a tns file?
when do u go for hash partitioning?
What is bulk load in oracle?
Explain what are the uses of rollback segment?
what is analyze command ? what kind of statistics does it generate?