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 171

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

User Name		Remember Me?
Password

Question Bank