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

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);

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.

Answer / manoj

select salary from emp
where salary = (select max (salary) from emp
where salary < (select max (salary) from emp
where salary < (select max (salary) from emp)));

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

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.

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>

Answer / saleem

select distinct(a.sal) from emp a where &3 = ( select count
(distinct(b.sal) from emp b where a.sal <= b.sal));

Answer / chanchal

SELECT MIN(salary) FROM Department WHERE salary IN
(SELECT TOP 3 salary FROM Department ORDER BY salary DESC)

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.

How to select the name of employee who is getting maximum sal with out using subquery

15 Answers  

Can a parameter be passed to a cursor?

0 Answers  

What is a View ?

3 Answers  

Explain index?

0 Answers  

What are the components of physical database structure in the Oracle database?

1 Answers  

Describe varray?

0 Answers  

I have a table emp. There is only one column in the table. In that , there are only three rows in that column. The value in the first row is 'A' and the value in the second row is 'B' and the third row is 'C'. Now, my question is , How will you write a select query to display the output as B C A Note: order by cannot be used coz it gives us output as CBA. But the output should be BCA.

4 Answers  

What is a connect identifier?

0 Answers  

What is flashback in Oracle?

0 Answers   MCN Solutions,

How to use "for" statements in oracle?

0 Answers  

what is a cartition product?

4 Answers  

What is a cluster Key ?

2 Answers