Re: Complicated GROUP BY

This will also work as long as the table isn't large or product is
indexed.
actually,its more likely product is indexed that criteria_1 or _2...

Note: in this case when there is a tie one provider is arbitrarily
selected

select mx.product,
mx.max_criteria_1,
(select provider from products_providers pp
where pp.product=mx.product
order by criteria_1 desc limit 1) as best_provider_1,
mx.max_criteria_2,
(select provider from products_providers pp
where pp.product=mx.product
order by criteria_2 desc limit 1) as best_provider_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;

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of dgront
Sent: Tuesday, July 08, 2008 6:12 PM
To: pgsql-general(at)postgresql(dot)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