CODEWITHSUNDEEP
oracle10g
WaelT
WaelT

Reputation: 25

View Top N-Results in Oracle

I'm trying to do a simple query to show best selling products in the company, specifically the top 3 ones. To get all results I'm running this query

select product_name, (qty_on_order - qty_on_hand) as Sales 
from products, product_inventory
where product_inventory.product_id = products.product_id
order by Sales Desc

This returns everything but ordered, but I need to view only the top 3 items, I used ROW_NUMBER but it didn't give me the results that I want.

Upvotes: 0

Views: 33

Answers (2)

ABHISHEK RANA
ABHISHEK RANA

Reputation: 327

You can solve this problem by using rank function also, which will be much easier. Try the following query

select rnk,product_name, (qty_on_order - qty_on_hand) as Sales from (select rnk,product_name, (qty_on_order - qty_on_hand) as Sales, rank() over (PARTITION BY product_name ORDER BY Sales DESC) as rnk from products, product_inventory) where rnk <= 3;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Is this what you want?

select *
from (select product_name, (qty_on_order - qty_on_hand) as Sales 
      from products join
           product_inventory
           on product_inventory.product_id = products.product_id
      order by Sales Desc
     ) p
where rownum <= 3;

Upvotes: 1

Related Questions