Complicated GROUP BY

From: dgront <dgront(at)chem(dot)uw(dot)edu(dot)pl>
To: pgsql-general(at)postgresql(dot)org
Subject: Complicated GROUP BY
Date: 2008-07-08 22:12:11
Message-ID: b418e622-af2f-4218-bfb7-b2de9b3debca@l64g2000hse.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Goldeneye Solutions Information 2008-07-09 00:49:49 Feature: FOR UPDATE SKIP LOCKED
Previous Message Nikolay Samokhvalov 2008-07-08 21:54:52 Happy birthday!