Single record of greatest n per group from a table with three identifiers

Question

I have a table in which each record is a single purchase made by a single client.

Purchases can be made in different categories of product and different geographical areas (each of these is a single field).

I can count how many purchases each client has in each combination of product/area like this

Select client_id, product_id, zone_id, COUNT(purchase_id)
Group by client_id, product_id, zone_id

From this I would like to get a table where each record is the client, product and zone with the highest number of purchases. So only one row per client.

How would I go about doing this?

I think I might be able to do it by using NOT EXISTS where there is no record with the same three identifiers and higher COUNT, but as this is part of a much larger query I'm afraid of performance issues.

I also figure I might be able to concatenate the three identifiers into a single one, but I need those identifiers to be in separate fields as I need them for a join in another query this will be part of.

Answer 1

Step 1: Generate a temp table with the 3 columns plus count as you mentioned.

Step 2: Apply a groupwise-max algorithm. Either follow the link you have or check out Groupwise-Max