find out the third highest salary?
Answers were Sorted based on User's Feedback
Answer / biswapratap singh
SELECT MIN(sal) FROM emp WHERE
sal IN (SELECT distinct TOP 3 sal FROM emp ORDER BY sal DESC)
| Is This Answer Correct ? | 214 Yes | 73 No |
Answer / neha
select max(sal) from emp where sal <(select max(sal)from emp
where sal<(select max(sal) from emp));
| Is This Answer Correct ? | 103 Yes | 31 No |
Answer / sanjeev kumar p
select * from emp e1 where (3-1) = (select count(distinct
(e2.sal)) from emp e2 where e2.sal>e1.sal)
| Is This Answer Correct ? | 99 Yes | 51 No |
Answer / arijit
donno whether the rest are correct ..but this one is:-
select max(sal) from emp where sal<(select max(sal) from emp
where sal<( select max(sal) from emp));
| Is This Answer Correct ? | 52 Yes | 17 No |
Answer / harmeet
Hi, this query will gives the third highest salary from table
select * from emp e
where 3 = (select count(sal) from emp
where e.sal < sal);
To dispay 5th highest salary, just change the number 3 to 5.
Like that we can find top N salaries.
I hope i'm not wrong.
| Is This Answer Correct ? | 44 Yes | 16 No |
Answer / akshita
select * from (select e.*,rownum r from (select distinct
sal from emp order by sal desc) e) where r = 3
| Is This Answer Correct ? | 43 Yes | 16 No |
Hi every body,
I give the standard format Query for this type of top most
and who are the top n people in the organization like that
If we take EMP table
Select * from EMP A where &n= (select count (distinct
(sal)) from EMP B where B.sal>A.sal)
If we give n value 1 or 2 or 3….etc then we gets top one,
top second, and top third person details.
This same query can we use top n people who are earning
maximum salaries in the Organization
Select * from EMP A where &n> (select count (distinct
(sal)) from EMP B where B.sal>A.sal)
| Is This Answer Correct ? | 38 Yes | 19 No |
Answer / punit chauhan
select * from emp where sal<(select max(sal) from emp
where sal<(select max(sal) from emp))
punit chauhan
iMS NOIDA
doing MCA
| Is This Answer Correct ? | 12 Yes | 0 No |
Answer / punit chauhan
select * from emp_record e1 where (3-1) = (select count ( distinct (e2.salary)) from emp_record e2 where e2.salary > e1.salary
| Is This Answer Correct ? | 12 Yes | 0 No |
Answer / selvaraj anna university coe
In ORACLE 10g,
SELECT * FROM employee x WHERE 3 = (SELECT COUNT(DISTINCT
salary)
FROM employee y WHERE x.salary <= y.salary);
| Is This Answer Correct ? | 16 Yes | 5 No |
What are various joins used while writing SUBQUERIES?
For a field in a repeating frame, can the source come from the column which does not exist in the data group which forms the base for the frame ?
Explain the use of show option in imp command.
How to define an anonymous procedure without variables?
When system tablespace is created?
What is the usage of analyze command in oracle?
Anyone have the Latest Oracle Dumbs?While u have please forard to narain1411@gmail.com
Explain drop constraint oracle?
What is a deadlock in SQL? How can you prevent it?
Are truncate and delete commands same? If so why?
what are indexes..how many types of index's are there and what are they?
7 Answers Green Info Solutions, TCS,
In Oracle Applications We are Using Sub Ledger Accounting. When we Transfer the Data all modules (AP,AR,CE,FA) to SLA We done Some customizations? What are They??