pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem obtaining MAX values FROM TABLE
2+ messages / 2 participants
[nested] [flat]
* Problem obtaining MAX values FROM TABLE
@ 2005-12-16 15:42 Michael Farewell <mfarewell@orange.net>
2005-12-21 17:56 ` Re: Problem obtaining MAX values FROM TABLE Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 2+ messages in thread
From: Michael Farewell @ 2005-12-16 15:42 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Problem obtaining MAX values FROM TABLE
2005-12-16 15:42 Problem obtaining MAX values FROM TABLE Michael Farewell <mfarewell@orange.net>
@ 2005-12-21 17:56 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Fuhr @ 2005-12-21 17:56 UTC (permalink / raw)
To: Michael Farewell <mfarewell@orange.net>; +Cc: pgsql-sql
On Fri, Dec 16, 2005 at 03:42:34PM -0000, Michael Farewell wrote:
> 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:
There are a few ways to do this, a couple of which were mentioned
recently in another thread. One is to use PostgreSQL's non-standard
DISTINCT ON:
SELECT DISTINCT ON (company_name)
b_id, company_name, product_count, product_type
FROM foo
ORDER BY company_name, product_count DESC;
Here's another way that uses standard SQL syntax:
SELECT b_id, company_name, product_count, product_type
FROM foo AS a
WHERE NOT EXISTS (
SELECT 1
FROM foo AS b
WHERE b.company_name = a.company_name
AND b.product_count > a.product_count
)
ORDER BY company_name;
These queries aren't quite the same, however: the first will return
exactly one row per company, while the second will return multiple
rows per company if multiple rows have the maximum product count.
For example, suppose you have this data, where two rows for company 1
have that company's maximum product count:
b_id | company_name | product_count | product_type
------+--------------+---------------+--------------
1 | company 1 | 1 | a
1 | company 1 | 2 | b
1 | company 1 | 2 | c
2 | company 2 | 3 | d
2 | company 2 | 4 | e
The first query (using DISTINCT ON) will return the following,
picking the row for company 1 based on whichever row among the
maximums is ordered first (which you can control with ORDER BY;
otherwise the row chosen will be indeterminate):
b_id | company_name | product_count | product_type
------+--------------+---------------+--------------
1 | company 1 | 2 | b
2 | company 2 | 4 | e
The second query will return both of the maximum rows for company 1:
b_id | company_name | product_count | product_type
------+--------------+---------------+--------------
1 | company 1 | 2 | b
1 | company 1 | 2 | c
2 | company 2 | 4 | e
--
Michael Fuhr
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-12-21 17:56 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-16 15:42 Problem obtaining MAX values FROM TABLE Michael Farewell <mfarewell@orange.net>
2005-12-21 17:56 ` Michael Fuhr <mike@fuhr.org>
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