SQL - maxvalue.sql Select the Nth Highest value from a table

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 28 of 169

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

Total Answers and Comments: 17

Last Update: October 10, 2007

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

Sorting Options

Page 1 of 2

« First

Last »

September 09, 2005 23:47:16

Rajendrakumar

RE: maxvalue.sql Select the Nth Highest value from a table

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.

Is this answer useful? Yes | No

November 11, 2005 05:46:49

Tarkeshwar

RE: Select the Nth Highest Salary from a table

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

Is this answer useful? Yes | No

January 02, 2006 02:16:46

ahmed

RE: maxvalue.sql Select the Nth Highest value from a t...

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

Is this answer useful? Yes | No

June 07, 2006 04:32:29

Hanumanth

RE: maxvalue.sql Select the Nth Highest value from a t...

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

Is this answer useful? Yes | No

August 02, 2006 07:33:55

gannesunil

Member Since: August 2006 Contribution: 6

RE: maxvalue.sql Select the Nth Highest value from a t...

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

regards

ganne

Is this answer useful? Yes | No

October 19, 2006 09:52:13

Srinivas

RE: maxvalue.sql Select the Nth Highest value from a t...

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

Is this answer useful? Yes | No

November 16, 2006 07:26:20

Saroj

RE: maxvalue.sql Select the Nth Highest value from a t...

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

e.sal >=f.sal.

Is this answer useful? Yes | No

November 21, 2006 12:15:44

Nitesh Gangrade

RE: maxvalue.sql Select the Nth Highest value from a t...

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.

Is this answer useful? Yes | No

November 24, 2006 00:18:42

saravanan.A

RE: maxvalue.sql Select the Nth Highest value from a t...

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.

Is this answer useful? Yes | No

November 27, 2006 02:52:28

#10

sganne

RE: maxvalue.sql Select the Nth Highest value from a t...

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

regards

ganne

Is this answer useful? Yes | No

Page 1 of 2

« First

Last »

Go To Top