How to SELECT top 10 products most sells?

Question

I have a table named "items" where are stored information about each product sold in each order, but I want to retrieve top 10 orders with max quantity of products sells. I've executed a SELECT statement to retrieve number of products sold in each orders but I don't know How I can retrieve only TOP 10 orders. Thanks.

SELECT   codigo_orden AS ORDER_ID, COUNT(codigo_producto) AS PRODUCTS_SOLD
FROM     cs_items
GROUP BY codigo_orden
ORDER BY 2 DESC;

But I just want to retrieve TOP 10 most products sold!! Something like that:

if there are ties in counts, do you only need one? and how should that be chosen? — Vamsi Prabhala, Mar 01 '17 at 21:30
I only need top 10 Orders doesn't matter if there are two orders with same x number of products. — , Mar 01 '17 at 21:39
Possible duplicate of [Oracle SELECT TOP 10 records](http://stackoverflow.com/questions/2498035/oracle-select-top-10-records) — mathguy, Mar 01 '17 at 22:35

BobC · Accepted Answer · 2017-03-01T21:47:06.033

2

If you are using Oracle 12c, you can use the row limiting clause, something like

SELECT   codigo_orden AS ORDER_ID, COUNT(codigo_producto) AS PRODUCTS_SOLD
FROM     cs_items
GROUP BY codigo_orden
ORDER BY 2 DESC
FETCH FIRST 10 ROWS ONLY

If you are not using 12c, then you can use a window function, for example

select order_id, products_sold
from (
    SELECT   codigo_orden AS ORDER_ID, 
             COUNT(codigo_producto) AS PRODUCTS_SOLD,
             rank() over (order by count(codigo_producto) ) as rnk
    FROM     cs_items
    GROUP BY codigo_orden
)
where rnk <= 10
order by products_sold

You might need to use dense_rank() vs rank(), depending on how you want to handle ties.

edited Mar 01 '17 at 21:47

answered Mar 01 '17 at 21:30

BobC

3,940
1
11
14

Thanks but I'm using Oracle 11g. – Mar 01 '17 at 21:38
@Rattlesnake. I have expanded my answer based on your comment. – BobC Mar 01 '17 at 21:47
From the OP's answer to follow-up questions, and from the example he provided, it seems he will need `row_number()` rather than `rank()` or `dense_rank()`. – mathguy Mar 01 '17 at 22:32

score 2 · Answer 2 · answered Mar 01 '17 at 21:43

2

Try the following:

select ORDER_ID, PRODUCTS_SOLD
from (
SELECT   codigo_orden AS ORDER_ID, COUNT(codigo_producto) AS PRODUCTS_SOLD
FROM     cs_items
GROUP BY codigo_orden
ORDER BY 2 DESC)
WHERE ROWNUM <= 10;

answered Mar 01 '17 at 21:43

Stephan Lechner

34,359
4
30
55

How to SELECT top 10 products most sells?

2 Answers2