Live ORACLE

rem execute this code in the Scott SCHEMA
SELECT   LEVEL,
       Max(sal)
FROM     emp
WHERE    LEVEL = &nth
CONNECT BY PRIOR sal > sal
GROUP BY LEVEL ;

------------------------------------

rem execute this code in the HR SCHEMA
SELECT   LEVEL,
       Max(salary)
FROM     employees
WHERE    LEVEL = &nth
CONNECT BY PRIOR salary > salary
GROUP BY LEVEL ;

SELECT sal ename 
FROM   emp
WHERE  sal IN (SELECT Min(sal)
FROM   (SELECT   DISTINCT sal FROM emp
ORDER BY sal DESC)
WHERE  ROWNUM = &num);  

SELECT salary FROM employees e1
WHERE  &n IN (SELECT Count(DISTINCT (e2.salary))
FROM   employees e2
WHERE  e2.salary > e1.salary);  

SELECT * FROM   (
SELECT ROWNUM rn , sal FROM   (
SELECT * FROM emp
ORDER BY sal DESC))
WHERE  rn = &x;  

SELECT salary FROM   (
SELECT salary FROM employees
ORDER BY salary)
WHERE  ROWNUM < &n;  

REM TO Find SECOND MAX VALUE FROM A TABLE
SELECT Max(salary) FROM employees WHERE salary IN 
(SELECT Max(salary) FROM employees);  

SELECT sal FROM   (
SELECT DISTINCT sal FROM emp
ORDER BY sal DESC)
WHERE  ROWNUM < &&n
MINUS
SELECT sal FROM   (
SELECT DISTINCT sal FROM emp
ORDER BY sal DESC)
WHERE  ROWNUM < (&n - 1);  

Select the Nth highest - lowest value from a table