-
-
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
-
Find out nth highest salary from emp table
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal < = b.sal);For Eg:-Enter value for n: 2SAL---------3700
-