select sal from emp
where sal=(select min(sal) from emp
where sal>(select min(sal) from emp
where sal>(select min(sal) from emp);

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

select * from emp e1 where (3-1) = (select count(distinct
(e2.sal)) from emp e2 where e2.sal>e1.sal)

SELECT MIN(sal) FROM emp WHERE 
sal IN (SELECT  distinct TOP 3  sal FROM emp ORDER BY sal DESC)

select empno,ename,sal from
(select empno,ename,sal from emp order by sal desc)
group by rownum,ename,sal having rownum=&n;
 from this query we can find top nth sal

select max(sal) from emp where sal <(select max(sal)from emp
where sal<(select max(sal) from emp));

select * from (select e.*,rownum r from (select distinct 
sal from emp order by sal desc) e) where r  = 3

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

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)

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

Hi everybody,
This is one way to get the third highest salary
select ROWNUM as RANK from (select * from emp order by sal) 
where ROWNUM<=3

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.

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

select *
  from (select distinct sal from emp order by sal desc)
 where rownum <= 3

SELECT * FROM
(
SELECT EMPLOYEECODE, SAL, Rank() OVER (ORDER BY SAL DESC ) 
RANKNEW FROM EMPLOYEE)
WHERE RANKNEW = 3

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.

Here is the correct query:

select ename,sal from emp e
where 3 = (select count(distinct(sal))+1 from emp 
where e.sal < sal);

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.

select * from emp where sal<(select max(sal) from emp
where sal<(select max(sal) from emp))

SELECT MAX(SAL) FROM EMP WHERE SAL NOT IN (SELECT MAX(SAL) 
FROM EMP UNION SELECT MAX(SAL) FROM EMP WHERE SAL NOT IN
(SELECT MAX(SAL) FROM EMP))

In ORACLE 10g,

SELECT * FROM employee x WHERE 3 = (SELECT COUNT(DISTINCT 
salary)
FROM employee y WHERE x.salary <= y.salary);

select name,salary from emp where salary=(select min(sal) 
from (select distinct TOP 3(salary) from emp order by 
salary desc;

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>

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

select sal from emp 
where sal<(select max(sal) from emp 
where sal in (select sal from emp
where sal < ( select max(sal) from emp)))