Interested to Buy Any Domain ? << Click Here >> for more details...
how to get second highest sal of emp table
- 6 Answers
- 10010 Views
- I also Faced
- E-Mail Answers
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 ? | 5 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 to define an anonymous block?
How to write numeric literals in oracle?
How to use "in" parameter properly?
What is a schema in oracle?
what is query and types of query
How to restrict the duplicate records in table valuesets
What do you mean by cdb and pdb in oracle 12c?
How to convert numbers to characters in oracle?
Explain the use of tables option in exp command.
What are the advantages of oracle?
What is cursor
What is a oracle database?
Oracle General 
