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> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mfarewell@orange.net
Subject: Re: Problem obtaining MAX values FROM TABLE
In-Reply-To: <000701c60257$567d9980$0100a8c0@xphome>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox