Souvanik Saha Souvanik Saha - 1 month ago 22
SQL Question

Find 3rd highest cost from table

This is catalog table:

enter image description here

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;


I got this error:


from keyword not found where expected

Answer

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 dense_rank():

select c.*
from (select c.*, dense_rank() over (order by cost desc) as seqnum
      from category c
     ) c
where seqnum = 3;