> 
> 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?


Is something like this what you're after?


select * from products_proivders 
order by criteria_1 desc limit 1


You can get the best providers for both criteria using union like this:


select * from (
select 'best_criteria_1' as name, product, provider, criteria_1, criteria_2
from products_proivders order by criteria_1 desc limit 1 ) x
union
select * from (
select 'best_criteria_2' as name, product, provider, criteria_1, criteria_2
from products_proivders order by criteria_2 desc limit 1 ) y








-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to