prathima

• Sep 19th, 2005

 

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

maverickwild

 Profile Answers by maverickwild 

• Nov 15th, 2005

 

Try this one

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)

nileshsingh

 Profile Answers by nileshsingh 

• May 18th, 2006

 

SELECT LEVEL,MAX(SAL) FROM <TABLE_NAME>

WHERE LEVEL = N

CONNECT BY PRIOR SAL>SAL

GROUP BY LEVEL;

vijayakumar

• Jun 4th, 2006

 

select * from
(
select rownum rn,sal from
(select distinct(sal) from emp order by sal)) x
where x.rn=5;

sreeprem

 Profile Answers by sreeprem 

• Jun 6th, 2006

 

thiswill work if u give the number instead of n for ex:

gives the Second highest Salary n the table

select min(salary) from xxyy where value in(select top 2 alary from xxyy order by value desc)

gannesunil

 Profile Answers by gannesunil 

• Aug 2nd, 2006

 

select * from (select rownum rn,sal from(select * from emp order by sal desc))

where rn=&m;

Sushil Kumar Vishwakarma

• Nov 7th, 2006

 

Best way is using CORRELATED SUBQUERY : as below

Select * From TableName T1 Where
    (N-1) = (Select Count(Distinct(E2.ColumnName)) From TableName  T2 Where   T2.ColumnName > T1.ColumnName)

For Required Case We can use it as :-

Select * From EMP T1 Where
    (2-1) = (Select Count(Distinct(E2.sal)) From EMP  T2 Where   T2.sal > T1.sal)

the_xxx

 Profile Answers by the_xxx 

• Mar 27th, 2007

 

The best way is to do using Rank.

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

tina_0091

 Profile Answers by tina_0091 

• Apr 3rd, 2007

 

hi,

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

appidixyz

 Profile Answers by appidixyz 

• Sep 20th, 2007

 

n=9th

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)

vidyamurali2003

 Profile Answers by vidyamurali2003 Questions by vidyamurali2003

• Dec 30th, 2008

 

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


Pls note where 6 is the ranknumber

Veena B

 Profile Answers by Veena B 

• Feb 5th, 2010

 

SELECT MIN(sal)
FROM emp
WHERE sal IN (SELECT sal
                    FROM (SELECT DISTINCT sal
                             FROM emp
                             ORDER BY sal DESC)
                    WHERE rownum <= n);
 

kishordey

 Profile Answers by kishordey 

• Feb 17th, 2010

 

select * from (SELECT emp_id, ROW_NUMBER() OVER(ORDER BY  salary DESC) AS salary, PostalCode 

FROM salary
)a 
where a.salary=n

a=1,2,3,4,................

Put a's value whatever u want............

vidya.R

 Profile Answers by vidya.R 

• Mar 2nd, 2010

 

SELECT MIN(salary) FROM (SELECT DISTINCT TOP 6 salary FROM empinfo ORDER BY salary desc) empinfo;

This query works perfectly in MS SQL SERVER

vidya.R

 Profile Answers by vidya.R 

• Mar 2nd, 2010

 

SELECT min(salary) FROM (SELECT DISTINCT TOP 6 salary FROM empinfo ORDER BY salary DESC) empinfo;

pradyumna k j

 Profile Answers by pradyumna k j 

• Jul 31st, 2010

 

SELECT salary

FROM employee

ORDER BY salary DESC

LIMIT n-1,1

amitsing2008

 Profile Answers by amitsing2008 

• Aug 10th, 2010

 

Empsal table data
700
500
100
900
400
200
600
750

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

Output=700

bodduramesh

 Profile Answers by bodduramesh 

• Oct 8th, 2010

 

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.

ranjith.avi

 Profile Answers by ranjith.avi 

• Jan 10th, 2011

 

Query:

select min(salary) from (select distinct salary from ranjith1 where salary is NOT NULL order by salary desc) where rownum<=&n;

Here the n is nth salary.
It will work perfectely..

Thanks
Ranjith

Sikindar

 Profile Answers by Sikindar 

• Mar 11th, 2011

 

select * from emp where sal=(

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal));

kethinenisarath

 Profile Answers by kethinenisarath 

• Mar 15th, 2011

 

select * from emp
where sal in(select max(sal) from emp
                    where level=&levelno
                    connect by prior sal>sal
                     group by level)




Thanks&Regards
Kethinenisarath

sankar babu

 Profile Answers by sankar babu 

• May 25th, 2011

 

select level,max(sal)

from emp

group by level;

asis sarma

• Jul 18th, 2011

 

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

Code

1.  

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
 

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)

Code
 

Chandan

• Aug 9th, 2011

 

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
 

Sethuraman

• Aug 19th, 2011

 

Hi Guys,

Code
 

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

srinivasmallabathula

 Profile Answers by srinivasmallabathula Questions by srinivasmallabathula

• Sep 13th, 2011

 

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.

dinesh.smhdr

 Profile Answers by dinesh.smhdr 

• Sep 13th, 2011

 

Code
 

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
span style="font-style: italic;">--WHERE ROWNUM =1

Hope this will workout as per your requirement

sameer

• Jan 10th, 2012

 

Code
 

Abhishek

• Jan 15th, 2012

 

Code
 

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.

Komalrad

 Profile Answers by Komalrad 

• Feb 16th, 2012

 

Code
 

bharathtele

 Profile Answers by bharathtele 

• Apr 26th, 2012

 

Select Emp_Name from emp_table where Id=(Select Emp_Id from Salary Order By salary Desc limit 0,1)

saketp

 Profile Answers by saketp 

• Apr 29th, 2012

 

This is the simplest of all :)

Code
span style="color: #F00;">"enter your nth value here";

rupesh

• May 4th, 2012

 

Code
 

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
 

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

rohitosu

 Profile Answers by rohitosu 

• Jul 30th, 2012

 

Code
 

bhavikgore

• Oct 27th, 2012

 

This will work for only highest salary n=1 but not work for 2nd and rest nth salary...
Did you have tried this?

Sivaji Bonu

• Nov 28th, 2014

 

Code
 

Note :: Here n is No of highest sal position ;i.e n=1,2,3.....

Sivaji Bonu

• Nov 28th, 2014

 

Hi bhavikgore,

I got the output and The given query work for Nth Height saL.......

Code
 

mohammed khan

• Dec 5th, 2014

 

Code
 

Dasish

• Dec 12th, 2014

 

Select a.empno, a.sal from emp a, emp b where a.sal <= b.sal group by a.empno, a.sal having count( distinct b.sal ) = &n

AKSHATHA

• Dec 25th, 2014

 

Use rank funtion -
SELECT * FROM (
SELECT empno,rank() OVER (ORDER BY sal desc) rn FROM emp)
WHERE rn = 1

Bhalchandra L.

• Apr 15th, 2015

 

Could you please provide me the step by step detailed execution logic of this query as this was asked in one of my interview .

Ejaz Ahmed

• Apr 19th, 2015

 

Here is a simple answer for finding nth salary or number, just change any number that you want to find after Where.

SELECT a.salary from employees a
where 4 = (SELECT DISTINCT COUNT(b.salary) from employees b where a.salary <= b.salary )

Nivetha Thinakaran

 Profile Answers by Nivetha Thinakaran 

• May 6th, 2015

 

Here is a step by step execution logic,
Consider the below employee table structure

Code
 

ksavinash

 Profile Answers by ksavinash 

• May 14th, 2015

 

N is the max value of salary like 1,2,3...~!

Code
 

Bose

• Jul 17th, 2015

 

Code
 

bb=nth value

SREENIVASULU

• Sep 10th, 2015

 

SELECT * FROM EMP E1 WHERE &N_1= (SELECT COUNT(DISTINCT(E2.SAL)) FROM EMP E2 WHERE E1.SAL

VISHWA

• Mar 10th, 2018

 

Here for ROWNUM =1 it will work but for ROWNUM = 2 and so on it will not work.

Silpa

• Apr 18th, 2018

 

Select employee_id,salary, rownumber from (Select employee_id,salary,row_number() over (order by salary desc) as rownumber from hr.employees)
where rownumber = n;