How to get the 10th highest cost in ORACLE?

Question

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?

Dark Knight · Accepted Answer · 2019-09-15T06:53:49.960

1

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:

Different ways to nth Highest in Oracle

edited Sep 15 '19 at 06:53

answered Sep 15 '19 at 05:04

Dark Knight

6,116
1
15
37

I got error like this: [Error] Execution (5: 13): ORA-00923: FROM keyword not found where expected – Ashwin Karki Sep 15 '19 at 05:55
Let me check on this, did you tried with table alias too. I have added `T1` – Dark Knight Sep 15 '19 at 06:38
it again showed me the same error near *,DENSE_RANK() – Ashwin Karki Sep 15 '19 at 06:43

Popeye · Answer 2 · 2019-09-15T06:29:22.867

0

You can use nth_value analytical function.

Select * from
(select t.*, DISTINCT NTH_VALUE(t.standard_cost,10)
       OVER (ORDER BY t.standard_cost desc
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS TENTH_HIGHEST
FROM PRODUCTS t)
Where standard_cost = tenth_highest;

Cheers!!

edited Sep 15 '19 at 06:29

answered Sep 15 '19 at 06:00

Popeye

35,427
4
10
31

How to get the 10th highest cost in ORACLE?

2 Answers2