I want to make sql qurey in laravel/php, to fetch top 10 distinct products purchased by customer.
My table structure looks like this:

Orders table (customer id, product id, etc....)
products table (product id, product name, etc....)

This is my attempted query:

SELECT products.* 
 FROM products 
 WHERE products.id=[SELECT DISTINCT (products.id) 
                     FROM orders 
                     WHERE customer.id=id->list(10)]

CodePudding user response：

I can see where you're going with your attempted query but unfortunately that won't give you top 10. Instead, you might not get any result at all:

SELECT products.* 
 FROM products 
 WHERE products.id=[SELECT DISTINCT (products.id) 
                 ^^^   FROM orders 
                       WHERE customer.id=id->list(10)]

The = means that you're looking for an exact match and your subquery suppose to returns 10 rows of data, which if you go with this operation, you'll receive this error.

Subquery returns more than 1 row

But if you change that to IN, you might receive this error instead

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

With your current attempt, your option is to do a JOIN. However, I'm wondering how do you get your top 10? I can see that you're looking for top 10 of products but base on what? Sales amount? Quantity ordered?

On that note, here's an example of top 10 products by quantity ordered.

SELECT P.* 
FROM Products P 
 JOIN ( SELECT product_id
         FROM Orders
        GROUP BY product_id
        ORDER BY SUM(Qty) DESC
    LIMIT 10) O 
ON P.id=O.product_id;

The subquery is not necessary but I'm imitating what you tried with a subquery albeit not exactly the same process. Here it is without subquery:

SELECT P.*
FROM Orders O 
 JOIN Products P ON O.product_id=P.id
GROUP BY product_id
ORDER BY SUM(Qty) DESC
LIMIT 10;

Or perhaps you're looking for top 10 by sales amount?

SELECT P.*
FROM Orders O 
 JOIN Products P ON O.product_id=P.id
GROUP BY product_id
ORDER BY SUM(UnitPrice*Qty) DESC
LIMIT 10;

Demo fiddle

CodePudding user response：

Sorry this is not going to work in MySQL.

To use distinct and top in the same query you need to add distinct first.

SELECT DISTINCT TOP 10 *  FROM Products;