Maxvalue.sql Select the Nth Highest value from a table

select level, max('col_name') from my_table where level = '&n' connect by prior ('col_name') >
'col_name')
group by level;
Example:
Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second highest salary:
-- select level, max(sal) from emp
-- where level=2
-- connect by prior sal > sal
-- group by level
  • Interview Candidate
  • 19
  • 7401

Showing Answers 1 - 19 of 19 Answers

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.

  Was this answer useful?  Yes

Tarkeshwar

  • Nov 11th, 2005
 

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

  Was this answer useful?  Yes

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...

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

Srinivas

  • Oct 19th, 2006
 

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

  Was this answer useful?  Yes

Saroj

  • Nov 16th, 2006
 

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

e.sal >=f.sal.

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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,

  Was this answer useful?  Yes

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)

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

MAINAK

  • Aug 10th, 2007
 

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


  Was this answer useful?  Yes

Raj Mohale

  • Sep 6th, 2007
 

If you want nth 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

  Was this answer useful?  Yes

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)

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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)

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions