product_id, COUNT(od2.product_id) AS rank
FROM order_detail od1
JOIN order_detail od2
ON od1.order_id = od2.order_id
WHERE od1.product_id = 4 AND od2.product_id != 4
GROUP BY od2.product_id
ORDER BY rank DESC;
CHAPTER 15 ?– PRODUCT RECOMMENDATIONS 465
?– Tip Placing a space between COUNT and the expression that follows makes MySQL generate an error, so
be careful.
The query now returns a list with the highest ranking products listed first:
product_id rank
---------- ----
10 3
5 2
43 2
23 1
25 1
28 1
12 1
14 1
If you don??™t need to see the rank, you can rewrite this query using the COUNT aggregate function
directly in the ORDER BY clause. You can also use the LIMIT keyword to specify how many
records you??™re interested in. If you want the top five products of the list, this query does the trick:
SELECT od2.product_id
FROM order_detail od1
JOIN order_detail od2
ON od1.order_id = od2.order_id
WHERE od1.product_id = 4 AND od2.product_id != 4
GROUP BY od2.product_id
ORDER BY COUNT(od2.product_id) DESC
LIMIT 5;
The results of this query are
product_id
----------
10
5
43
23
25
CHAPTER 15 ?– PRODUCT RECOMMENDATIONS 466
?– Note Be aware that you may get different results if you try this query using your database, even if you
have the same data as we have. When the sorting criteria that you specify using ORDER BY is not specific
enough, the database engine chooses the sorting method that is most convenient for it, which really means
that you get the records that are the quickest and easiest for the database to retrieve.
Pages:
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585