find out the third highest salary?
Answers were Sorted based on User's Feedback
Answer / rajdevar
Guys
i have executed this query in sql plus.This is correct
Select * from EMP_USER A where n-1 = (select count
(distinct (sal)) from EMP_USER B where A.sal<B.sal)
where n=3
reason for using n-1 is below:
1.We are using correlated sub query.so sal value from each
row in outer query(EMP_USER A) is compared with sal of all
the rows in EMP_USER B
eg:
ENAME SAL
----- -----
SMITH 800
KING 5000
FORD 3000
here third highest is 800.so when executing this 800 from
outer query is compared with 800,5000,3000 in inner query
which returns a count(distinct(sal) = 2
if you use n instead of n-1 you get a empty result.
Let me know if this is correct
Is This Answer Correct ? | 13 Yes | 5 No |
Answer / vishal
Here is the correct query:
select ename,sal from emp e
where 3 = (select count(distinct(sal))+1 from emp
where e.sal < sal);
Is This Answer Correct ? | 12 Yes | 5 No |
Answer / sudheer
Guys Few of you have submitted an optimal query.
select distinct sal from emp A where 3 = (select count(sal)
from emp B where a.sal > b.sal)
However the integer 3 will not give the third highest salary
as expected. Point should be noted that the row starts with
zero.
so the actual result will the fourth highest salary with the
above query.
Is This Answer Correct ? | 7 Yes | 3 No |
Answer / kss
Hi every one,
i know that we can get that thr above quer..
one can try using analytic function giving ranks to the
salary column
select sal,dense_rank() over(order by sal desc) rank from
emp
Is This Answer Correct ? | 18 Yes | 15 No |
Answer / nehal
select e.sal from employee where n-1=(Select count(distinct
sal) from emp where sal>e.sal)
Put n=3 for the 3rd highest salary.
Is This Answer Correct ? | 10 Yes | 7 No |
Answer / selvaraj anna university coe
In Oracle 9i:
-------------
SQL> SELECT MAX(salary) FROM emp WHERE LEVEL=&no CONNECT BY
PRIOR Salary>salary;
Enter value for no: 3
old 1: SELECT MAX(SALARY) FROM EMP WHERE LEVEL=&no
CONNECT BY PRIOR SALARY>SALARY
new 1: SELECT MAX(SALARY) FROM EMP WHERE LEVEL=3 CONNECT
BY PRIOR SALARY>SALARY
MAX(SALARY)
-----------
500000
SQL>
Is This Answer Correct ? | 8 Yes | 5 No |
Answer / chanchal
SELECT MIN(salary) FROM Department WHERE salary IN
(SELECT TOP 3 salary FROM Department ORDER BY salary DESC)
Is This Answer Correct ? | 4 Yes | 2 No |
Answer / raj
Answer 28 is the dumbest way of writing the query.what
would you write if i want the 25 highest salary? repeat
this satement 25 times.
Is This Answer Correct ? | 3 Yes | 1 No |
What is oracle and what are its different editions?
What is hash cluster in oracle?
How to use "while" statements in oracle?
How do you find current date and time in oracle?
Explain the use of file option in exp command.
How to convert the date format from dd/mm/yyyy to mm/dd/yyyy. (for eg). 22/05/2008 to 05/22/2008. when u use to_char(), it tells the month is invalid. how to resolve this problem.tel with eg.
What is flashback Query ? And Use ?
5. Display full details for the creditor/s who has received the single largest payment. Do not use a table join or set operator anywhere in your query.
How to define a sub function?
I creat Credit memo in AR. Now i want revers the Credit Memo.how you can revers that what out any aditional entry.
What is Database Link ?
What is Normalization ?
55 Answers ACS, Genpact, Graphix Solution, IBM, Keane India Ltd, TCS, Vault,