Including ties in MySQL without a subquery?
Is there a better way to include ties in MySQL? I tried several things, but this is the only query that showed the ‘Top 2 products purchased by each customer with ties’. Basically, is there a way to do this without a subquery?
WITH tops AS (SELECT CONCAT(c.first_name, " ", c.last_name) AS Customer, brand, SUM(quantity) AS amount, DENSE_RANK() OVER(PARTITION BY c.last_name, c.first_name ORDER BY SUM(quantity) DESC) AS rk FROM customers c JOIN orders o ON c.id = o.customer_id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON p.id = oi.product_id GROUP BY Customer, brand ORDER BY c.last_name, amount DESC) SELECT Customer, brand, amount FROM tops WHERE rk <=2 ; 
