From: "Michael Farewell" <mfarewell@orange.net>
To: <pgsql-sql@postgresql.org>
Subject: Problem obtaining MAX values FROM TABLE
Date: Fri, 16 Dec 2005 15:42:34 -0000
Message-ID: <000701c60257$567d9980$0100a8c0@xphome>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Importance: Normal

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=20
------------------------------------------------
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=20
look like this:

b_id	company_name	 product_count   product_type=20
------------------------------------------------
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