选择一个列中第n大的值

rem Purpose:    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
 
rem Purpose:    Select the Nth lowest value from a table

select level, min('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 lowest salary:
--
-- select level, min(sal) from emp
-- where level=2
-- connect by prior sal < sal
-- group by level