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 |
How do you tell what your machine name is and what is its IP address?
How to load data from external tables to regular tables?
How to revoke create session privilege from a user in oracle?
What is the use of Redo Log Information ?
Explain the use of online redo log files in oracle.
What is a Data File ?
Can u make a synonym for deptno=10 only from emp table.
Explain an exception?
WHT ARE THE AGGREATE FUNCTIONS?
What are the differences between lov and list item?
Explain an index segment?
What happens to the current transaction if a ddl statement is executed?