This is catalog table:
I have to find 3rd highest cost from this table .
I wrote this:
SELECT TOP 1 COST FROM CATALOG WHERE COST IN
(SELECT DISTINCT TOP 3 COST FROM CATALOG ORDER BY COST DESC)
ORDER BY COST ASC;
from keyword not found where expected
Oracle does not recognize
TOP. You can do what you want but it requires additional subqueries (unless you are using Oracle 12c+):
SELECT MIN(COST) FROM CATALOG WHERE COST IN (SELECT COST FROM (SELECT DISTINCT COST FROM CATALOG ORDER BY COST DESC ) c WHERE rownum <= 3 );
Note: As tempting as it might be, you cannot use
WHERE rownum = 3 in the subquery. You can review the documentation for the explanation, but basically
rownum does not get incremented until a row is placed in the result set. So,
rownum = 1 always needs to be in the result set.
Finally, the normal way to do this in any (reasonable) database is to use
select c.* from (select c.*, dense_rank() over (order by cost desc) as seqnum from category c ) c where seqnum = 3;