Select within select

[purnimamca]

to find nth highest salary

select distinct (a.sal) from emp a where &n=select (count(distinct b.sal) from emp b where a.sal<=b.sal);

[krishnaindia2007]

You can write in this way also

SELECT ename,job,sal
FROM
(
SELECT *
FROM emp
ORDEY BY sal DESC
)
WHERE ROWNUM <=10;

[priyasp_msit]

You can write in this way also

SELECT ename,job,sal
FROM
(
SELECT *
FROM emp
ORDEY BY sal DESC
)
WHERE ROWNUM <=10;

this is called inlineview.