rem -----------------------------------------------------------------------
rem Filename:   maxvalue.sql
rem Purpose:    Select the Nth highest value from a table
rem Date:       18-Apr-2001
rem Author:     Deepak Rai, SSE, Satyam Computer Services Ltd. India
rem -----------------------------------------------------------------------

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