Search code examples
sqloracletop-n

How to get the 10th highest cost in ORACLE?

I have a table products and i have column standard_cost and I want to get the standard cost of the product which lies in 10th highest position. So far I tried 

SELECT t1.* 
FROM (SELECT  STANDARD_COST 
      FROM OT.PRODUCTS 
      ORDER BY STANDARD_COST DESC) t1  
WHERE ROWNUM=1 
ORDER BY t1.STANDARD_COST ASC;

But it's giving me the same value when I first sorted descending of inner query and then ascending in ORACLE. What is the problem in my query?

Solution

  • There are any ways to find nth highest cost.

    SELECT T1.STANDARD_COST
FROM (
    SELECT STANDARD_COST, DENSE_RANK() OVER (ORDER BY STANDARD_COST DESC) nth_highest_cost 
    FROM PRODUCTS
) T1
WHERE nth_highest_cost = 10;

    You can look for other methods from following reference: