Problem obtaining MAX values FROM TABLE

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

Responses

Browse pgsql-sql by date

  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