Rajendrakumar

• Sep 9th, 2005

 

Nth Max value in a table 
 
For Exaple A Table ABC 
 
Salay 
------ 
10 
20 
30 
40 
 
I Need the Nth Max Salary of the Table ABC.

Tarkeshwar

• Nov 11th, 2005

 

SELECT     *
FROM         TableName E1
WHERE     (N =
                          (SELECT     COUNT(DISTINCT (E2.sal))
                            FROM         TableName E2
                            WHERE      E2.sal >= E1.sal))

ahmed

• Jan 2nd, 2006

 

this is one of the ways to find out the Nth highest salary :select * from(select salary,rank() over(order by salary desc) as r from (select distinct salary from employees)) where r=&Ntry out this...

Hanumanth

• Jun 7th, 2006

 

We can use below query to get Nth Highest or lowest sal

select sal,ename from emp where sal= (select min(sal) from (select distinct sal from emp  order by sal desc) where rownum <= &num)
/

gannesunil

 Profile Answers by gannesunil 

• Aug 2nd, 2006

 

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

regards

ganne

Srinivas

• Oct 19th, 2006

 

select * from emp a
where &n = (select count(distinct(b.sal)) from emp b where a.sal > b.sal)
/

Saroj

• Nov 16th, 2006

 

we have to put = sign where compair both column i.e

e.sal >=f.sal.

Nitesh Gangrade

• Nov 21st, 2006

 

Select * from Employee e1 where N-n = (select count(distinct(e2.salary)) from Employee e2 where e2.salary>e1.salary;

Where N = no of rows in table that we can find through

N=Select count(distinct(*)) from employee;

n = nth max record.

saravanan.A

• Nov 24th, 2006

 

select distinct (fieldname) from tablename order by fieldname desc limit 1,1

By executing this query u can get the second maximum value of the table.

sganne

• Nov 27th, 2006

 

select * FROM (SELECT ROWNUM rn,sal from(SELECT * FROM emp ORDER BY sal DESC)) WHERE rn=&x; try likr this

regards

ganne

srilaxmi

• Jan 23rd, 2007

 

Hi to all,It is good .But I am getting error at where condition.Why result value is not initalize to N.select * from emp awhere n = (select count(distinct(b.salary)) from emp b where a.salary > b.salary)plz clarify my doubt.why it is giving error.plz reply to my mail.bye,

Geeta Pavate

• Feb 27th, 2007

 

select min(sal) from employee where sal in(
select top &N sal from employee group by sal order by sal desc)

naresh

• Apr 17th, 2007

 

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

In this I find 3rd maximum if you want find 2nd maximum you can place top 2

Soni

• May 17th, 2007

 

This is the code to find the nth highest from a table

select * from (select salary from(select salary from employee order by salary) where rownum<=&n) where rownum <=1


first it finds the salary in desending order after that it extracts the first nth values and in the last displays the nth value

MAINAK

• Aug 10th, 2007

 

SELECT MAX(FIELDVALUE)
FROM TABLENAME
WHERE FIELDVALUE < (SELECT MAX(FIELDVALUE) FROM TABLENAME)

Raj Mohale

• Sep 6th, 2007

 

If you want n^th sal from table please check this you will get, You
have to replace the number which you want

select min(sal1) from emp_details where sal1 in (select distinct top 2 sal1
from Emp_Details order by sal1 desc)

Best of Luck

Raj Mohale

rgeorge

• Oct 10th, 2007

 

SELECT MAX () FROM WHERE IN (SELECT TOP n FROM ORDER BY ASC)OR SELECT MIN () FROM WHERE IN (SELECT TOP n FROM ORDER BY DESC)

Avashisth

 Profile Answers by Avashisth 

• Feb 20th, 2010

 

-- To get nth Highest
select distinct salary

from (select salary,dense_rank() over(order by salary desc) as sal from Table )where

sal = &n

--To get nth Lowest

select distinct salary

from (select salary,dense_rank() over(order by salary) as sal from Table )

where sal = &n

nklpunitha

 Profile Answers by nklpunitha 

• Feb 20th, 2010

 

Try the below query, it will give the correct output
Select * from emp where salary = (select min(salary) from (select distinct salary from emp order by salary desc) where rownum < n)