if criteria_1 and _2 are indexed:
select
mx.product,
p1.provider as best_provider_1,
mx.max_criteria_1,
p2.provider as best_provider_2
mx.max_criteria_2
from
(select
product,
max(criteria_1) as max_criteria_1,
max(criteria_2) as max_criteria_2
from
products_providers group by product) mx,
products_providers p1,
products_providers p2
where
p1.criteria_1=mx.max_criterial_1
and p1.criteria_2=mx.max_criterial_2
note: you'll get a cross-product if there any ties for best provider
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of dgront
Sent: Tuesday, July 08, 2008 6:12 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Complicated GROUP BY
Dear All,
I have the following problem with grouping: I want to know the maximum
in a group as well as the maximal element. Example:
I have a table products_providers:
product | provider | criteria_1 | criteria_2
I have a number of products, each of them from a several providers.
Each product is described by two numeric values. I can easily select
the best value for each product by a given criteria, like:
select product, max(criteria_1) from products_providers group by
product;
but I need to know the best-scoring provider as well.
Result I need should look like:
product | best_provider_1 | best_criteria_1 | best_provider_2 |
best_criteria_2
If it counts results may be split into two tables: one for the first
and the other for the second criteria
Can you help me with a painless solution?
Dominik
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
