Search 800 + Posts

Mar 25, 2013

Query to Get TOP 5 Item Ordered by Customer


Below is query to Get TOP 5 Item Ordered by Customer in last 250 days




select ordered_item,Total_Qty_Ordered, sold_to_org_id
FROM (
SELECT  l.ordered_item,SUM(l.ordered_quantity) Total_Qty_Ordered, l.sold_to_org_id,RANK() OVER ( ORDER BY SUM(l.ordered_quantity) DESC) "RANK"
  from oe_order_lines_all l
   where 1=1--h.header_id = l.header_id
   and l.creation_date between sysdate-250 and sysdate
   and l.sold_to_org_id = &Enter_Cust_account_id
   group by l.ordered_item,l.sold_to_org_id
)
where  RANK <= 5
order by RANK ASC

1 comment: