SELECT mod(ROWNUM,2),sup_cd, qtn_price FROM (SELECT sup_cd, qtn_price FROM TB_B_sup_price C1 ORDER BY qtn_price DESC) WHERE ROWNUM<=n+1 MINUSSELECT mod(ROWNUM,2),sup_cd, qtn_price FROM (SELECT sup_cd, qtn_price FROM TB_B_sup_price C1 ORDER BY qtn_price DESC) WHERE ROWNUM<=n-1 order by qtn_price desc

select sal from (select distinct(sal) from emp where sal is NOT NULL order by sal dsc) where rownum =n

sayeed

Jan 2nd, 2006

directly we can not give the condition as rownum=2....this is the most optimal soluton for the given problemselect * from (select rownum r,salary from (select distinct(salary)from employees where salary is NOT NULL order by salary desc)) where r=2try out this.

Bharath B S

Jan 18th, 2006

we cant use rownum=n so u can use this select min(sal) "nth highest sal" from emp where sal in (select sal from (select DISTINCT(sal) from emp order by sal desc) where rownum<=n)

Bharath B S

Jan 18th, 2006

we cant use rownum=n so u can use this select min(sal) "nth highest sal" from emp where sal in (select sal from (select DISTINCT(sal) from emp order by sal desc) where rownum<=n)

SELECT * FROM (select COL1,COL2, dense_rank() OVER (order BY COLUMN_TO_SELECT_NTH_VAL) rank from TABLE_NAME) WHERE rank=N;

NITIN R

Mar 30th, 2007

Try this:

SELECT RANK,SALARY FROM(SELECT ROWNUM AS RANK,SALARY FROM(SELECT DISTINCT SALARY FROM EMPLOYEES WHERE SALARY IS NOT NULL ORDER BY SALARY DESC)) WHERE RANK =n;

sunil

Mar 30th, 2007

can u try this it is much better than this i think select * from emp(select rownum rn,sal from (select * from emp order by sal desc)) where rn=&m

u can dynamically enter which highest sal u can get it

Use the top-analysis feature which introduce from oracle 8i:

SELECT rownum, empno, ename, sal FROM (SELECT empno, ename, sal from emp ORDER BY sal desc ) WHERE rownum < n; (n=any valid integer)

Thanks, Tina K.

Animesh

May 25th, 2007

Hi I think the best way of doing this is given here........

1 select b.sal from 2 (select distinct sal from emp) a, 3 (select distinct sal from emp) b 4 where a.sal>=b.sal 5* group by(b.sal) having count(b.sal)=n;

try it ......to replace n with the number u want

nidhi

Jul 11th, 2007

Hi,

try this...

select max(sal) from emp where rownum<=&v order by sal desc;

soumyapradh

Jul 21st, 2007

This one is the most appropiate answer SELECT LEVEL, MAX(Sal) FROM Emp WHERE LEVEL = &LevelNo CONNECT BY PRIOR Sal > Sal GROUP BY LEVEL

varma indukuri

Jul 30th, 2007

This also works:

select * from

(select * from

(select * from

(select distinct(salary)

from employee

order by 1 desc )

where rownum < (&N+1) )

order by 1 asc )

where rownum=1

yadav.hitendra

Aug 13th, 2007

(1)

select * from empl a where 9=(select count(*) from (select distinct salary from empl)b

where b.salary>=a.salary);

---------------------------------- In this query in mqin query use 9= for finding 9th highest salary employee, 9>= for finding nine high salary employees, 9<= for finding nine low salary employees. ---------------------------------- or we can use this query also- ---------------------------------- (I) for finding 9th highest salary:-

select max(salary) from (select * from (select distinct salary from empl order by salary desc)where

rownum<=9);

(II) for finding nine high salary employees :-

select * from (select distinct salary from empl order by salary desc)where

rownum<=9;

(III) for finding nine low salary employees :-

select * from (select distinct salary from empl order by salary )

where rownum<=9;

vivek singh

Aug 15th, 2007

To find second highest salary we cannot use = operator in TOP N analysis

select rownum,sal from (select sal from emp where sal <(select max(sal) from emp) order by sal desc) where rownum < 2

select a.empno,rank() over(order by sal) sal_rank from emp a select * from( select a.empno,rank() over(order by sal) sal_rank from emp a )where sal_rank=9;

heloevry1

Sep 21st, 2007

To get nth highest sal: This is the easy way to get that:

for eg: 5th

select max(sal) from emp where sal not in (select top 4 sal from emp order by sal desc)

OR

select min(sal) from emp where sal in (select top 5 sal from emp order by sal desc)

Vikram

Oct 3rd, 2007

Shortest Query

SELECT MIN(e.salary) FROM (SELECT salary
FROM employee ORDER BY salary DESC) e WHERE rowid <= n;

susi

Oct 25th, 2007

select sal from (select distinct sal from emp order by desc) where rownum < &&n minus select sal from (select distinct sal from emp order by desc) where rownum < (&n -1)

SELECT v.sal FROM (SELECT TO_NUMBER(salary) sal,rank() over (ORDER BY TO_NUMBER(salary) DESC) ranksal FROM employee ORDER BY 1 DESC)v WHERE v.ranksal=6

Query to find second highest salary from table Empsal

mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary<(select max(salary) from Empsal));

Output=750

Query to find third highest salary from table Empsal

mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary<
>(select max(e1.salary) from Empsal e1
>where e1.salary IN(
>select e2.salary from Empsal e2 where
>e2.salary<(select max(salary) from Empsal))));

select column1,column2 from tablename(ex:sailors s1) where n-1=( select count(*) from sailors s2 where s2.rating>s1.rating);

Explanation : The sub query selects the count number in descending order just like an array. Then array like indexes matches to n-1 to give exact result; Request:please correct it if any mistakes. i have executed it and got good results.

How does the following query work to find the nth highest salary

Code

SELECTDISTINCT(A.SALARY)FROM EMPLOYEE A

WHERE &SALARY =(SELECT COUNT(DISTINCT(B.SALARY))FROM EMPLOYEE B

WHERE A.SALARY<=B.SALARY);

thanks
Asis

bhaskar sit mca

Jul 25th, 2011

You can find Nth highest salary in emp table using the following query.You can replace the Nth value with what u want..... select min(sal) max from (select * from emp order by sal desc) where rownum<=N;

bhaskar sit

Jul 25th, 2011

To find Nth highest sal in emp table......use the following query .You can replace it N with what ever number u want......
select min(sal) from (select * from emp order by sal desc) where rownum<=N; for example for 5th highest salary:
select min(sal) max from (select * from emp order by sal desc) where rownum<=5;

bhaskar

Jul 25th, 2011

To find Nth highest sal in emp table......use the following query .You can replace it N with what ever number u want......
select min(sal) from (select * from emp order by sal desc) where rownum<=N; for example for 5th highest salary:
select min(sal) max from (select * from emp order by sal desc) where rownum<=5;

Aarti Vyas

Jul 25th, 2011

Code

SELECT Top n DISTINCT Emp_Sal

FROM Emp

ORDERBY Emp_Sal DESC

Prabakaran

Jul 26th, 2011

select sal from (select sal from emp where sal is not null order by sal desc) where rownum=n

Rohit kumar

Jul 31st, 2011

Select * from < table name > e where
row num=(select count (distinct coloumn name) from < table name > where e.coloumn name < = coloumn name) For example:
we have to find the six th maximum salary from the table employee Ans: select * from employee e where
6=(select count(distinct salary ) from employee where e.salary < = salary)

SELECT LEVEL,MAX(sal) --SELECTING level and highest salary
FROM EMP
CONNECT BY PRIOR sal > sal --checking nth highest salary by optimiser
WHERE LEVEL=&LEVEL -- allow user to enter a number to see thatmuch highest salary
GROUP BY LEVEL;

srimanta sahoo

Aug 17th, 2011

Code

SELECT Level , Max(sal)FROM<Table_Name>

WHERE Level=&Level

Connect BY Prior sal>sal

GROUPBY Level

Sethuraman

Aug 19th, 2011

Hi Guys,

Code

SELECTMIN(sal)

FROM

(

SELECT

DISTINCT(sal)

FROM

prsn t1

ORDERBY sal DESC

FETCH FIRST N ROWS ONLY

)AS A

This query is efficient way to compare all other queries.
Because all other queries using Joins. But I have used sub query concept.

gopal sharma

Aug 27th, 2011

select min(sal)
from table_name
where sal in (select top N sal
from table_name
order by sal desc);

For this 2 ways are there.
1.select sal from (select sal from emp order by sal desc) where rownum <= n
minus
select sal from (select sal from emp order by sal desc) where rownum < n 2.select sal from emp e1 where (n-1)=select count(*) from emp e2 where e1.salary > e2.salary But first approach is best approach because in second case one row of first table is compared with all the rows of second table.So performance will be reduced.

SELECTROWNUM,empno,ename,sal,job FROM(SELECT empno,ename,sal,job FROM emp ORDERBY sal DESC)GROUPBY empno,ename,sal,job WHEREROWNUM=&n

vamsi krishna devineni

Nov 27th, 2011

select * from emp e1 where &n=(select count(*) from emp e2 where e1.sal < e2.sal); in this query n=0 means first highest
n=1 means second highest
n=2 means third highest

kranthi swaroop

Dec 2nd, 2011

Code

SELECTDISTINCT MAX(SAL),ENAME

FROM EMP

WHERE SAL =(SELECT MAX(SAL)FROM EMP)

--WHERE ROWNUM =1

GROUPBY ENAME

ORDERBY1ASC

Hope this will workout as per your requirement

sameer

Jan 10th, 2012

Code

SELECT min(sal)FROM(SELECTDISTINCT sal FROM emp ORDERBY sal DESC)WHERE rownum<=N

Abhishek

Jan 15th, 2012

Code

SELECT ename,empno,sal FROM(

SELECT ename,empno,sal FROM scott.emp ORDERBY sal DESC)

GROUPBY rownum,ename,empno,sal

HAVING rownum =5

Prabhu Shankar

Jan 17th, 2012

Hi.. your query returns fisrt 5 max salaries but the question is nth maximum salary so your query will be : select min(salary) Salary from (select salary from employees where salary is NOT NULL order by salary desc) where rownum < = 5 here it will show 5th max salary. Try this and let me know if my query is wrong.

FROM(SELECT salary,last_name FROM employees ORDERBY salary DESC)

WHERE rownum<=n;

where n is the number of person you want to show.
eg: if n=3,itll show first three values.

nain aggarwal

May 29th, 2012

Easiest way to find the nth highest salary

Code

SELECT salary FROM employees WHERE salary=

(SELECTMAX(salary)FROM(SELECTDISTINCT salary FROM employees WHERE rownum<=n-1ORDERBY salary DESC));

n=nth employee salary

Rahman,TCS

May 31st, 2012

May be we can have the right results with the above queries as mentioned above.... pls try to use the one below to understand clearly to get the 3rd max sal. Select Esal from (Select Esal from Emp order by Desc) where Rownum<4
Minus
Select Esal from (Select Esal from Emp order by Desc) where Rownum<3

## Find out nth highest salary from emp table

## Answer Question

Click here to Login / Register your free account

## Related Answered Questions

## Related Open Questions