Martín Vergara Martín Vergara - 23 days ago 8
SQL Question

SQL strategy to fetch maximum

Suppose I have these three tables:

enter image description here

I want to get, for all products, it's product_id and the client that bougth it most times (the biggest client of the product).

I solved it like this:

SELECT
product_id AS product,
(SELECT TOP 1 client_id FROM Bill_Item, Bill
WHERE Bill_Item.product_id = p.product_id
and Bill_Item.bill_id = Bill.bill_id
GROUP BY
client_id
ORDER BY
COUNT(*) DESC
) AS client
FROM Product p


Do you know a better way?

Answer

I was going to submit pretty much the same thing as @Squirrell only with a Common Table Expression [CTE] rather than a derived table. So I wont duplicate that but there are some learning points concerning your query. First is IMPLICIT JOINS such as FROM Bill_Item, Bill are really easy to have uintended consequences (one of many questions: Queries that implicit SQL joins can't do?) Next for the Calculated column you can actually do this in a OUTER APPLY or CROSS APPLY which is a very useful technique.

So you could re-write your method as follows:

SELECT *
FROM
    Product p
    OUTER APPLY (SELECT TOP 1 b.client_id
             FROM
                Bill_Item bi
                INNER JOIN Bill b
                ON bi.bill_id = b.bill_id
             WHERE
                bi.product_id = p.product_id
             GROUP BY
                b.client_id
             ORDER BY
                COUNT(*) DESC) c

And to show you how squirell's answer can still include products that have never been sold all you need to do is join Products and LEFT JOIN to other tables:

;WITH cte AS (
    SELECT
       p.product_id
       ,b.client_id
       ,ROW_NUMBER() OVER (PARTITION BY p.product_id ORDER BY COUNT(*) DESC) as RowNumber
    FROM
       Product p
       LEFT JOIN Bill_Item bi
       ON p.product_id = bi.product_id
       LEFT JOIN Bill b
       ON bi.bill_id = b.bill_id
    GROUP BY
       p.product_id
       ,b.client_id
)

SELECT *
FROM
    cte
WHERE
    RowNumber = 1

Techniques used in some of these that are useful.

  • CTE
  • APPLY (Outer & Cross)
  • Window Functions