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

nklpunitha

 Profile Answers by nklpunitha Questions 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)

Avashisth

 Profile Answers by Avashisth Questions by Avashisth

• Feb 20th, 2010

-- To get nth Highestselect distinct salary from (select salary,dense_rank() over(order by salary desc) as sal from Table )where sal = &n--To get nth Lowestselect distinct salaryfrom (select salary,dense_rank() over(order by salary) as sal from Table ) where sal = &n

Answer Question  Select Best Answer