From: | "Michael Farewell" <mfarewell(at)orange(dot)net> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Problem obtaining MAX values FROM TABLE |
Date: | 2005-12-16 15:42:34 |
Message-ID: | 000701c60257$567d9980$0100a8c0@xphome |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alexandre Gonçalves Jacarandá | 2005-12-16 16:03:14 | Re: Help on function creating |
Previous Message | Alvaro Herrera | 2005-12-16 13:57:32 | Re: Need SQL Help Finding Current Status of members |