I am having a problem with a query, I have a view which produces something
like this:
b_id company_name product_count product_type
------------------------------------------------
29 "company 1" 1 "a"
29 "company 2" 1 "b"
29 "company 3" 3 "a"
27 "company 4" 1 "c"
27 "company 4" 4 "d"
24 "company 5" 3 "a"
24 "company 5" 5 "c"
24 "company 5" 2 "d"
-------------------------------------------------
I need to write a query which returns each company together with the
highest product_count and its associated product type so the result should
look like this:
b_id company_name product_count product_type
------------------------------------------------
29 "company 1" 1 "a"
29 "company 2" 1 "b"
29 "company 3" 3 "a"
27 "company 4" 4 "d"
24 "company 5" 5 "c"
-------------------------------------------------
I have tried the following query:
SELECT company, MAX(type_count), product_type FROM buyer_product_frequencies
GROUP BY company, product_type
But in this case it just produces the same results as are in the first
table. If I drop product_type from the query I get the right result but I
don't have the product type which I need. This problem is driving me mad! so
any assistance would be greatly appreciated.
Many thanks,
Mike Farewell