CODEWITHSUNDEEP
sqloracle-databasegroup-bysql-order-by
user6611026
user6611026

Reputation:

How to SELECT top 10 products most sells?

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;

enter image description here

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

enter image description here

Upvotes: 1

Views: 15464

Answers (2)

BobC
BobC

Reputation: 4416

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.

Upvotes: 2

Stephan Lechner
Stephan Lechner

Reputation: 35154

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;

Upvotes: 1

Related Questions