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

GeekInterview.com > Interview Questions > Oracle > SQL

Print |

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

Answer:

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

June 06, 2006 04:32:29	#4
Hanumanth	Member Since: Visitor Total Comments: N/A
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) /