Lists: | pgsql-general |
---|
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 |
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
From: | "Hoover, Jeffrey" <jhoover(at)jcvi(dot)ORG> |
---|---|
To: | "dgront" <dgront(at)chem(dot)uw(dot)edu(dot)pl>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Complicated GROUP BY |
Date: | 2008-07-11 18:50:57 |
Message-ID: | E92C2B1CB12A7A4683697273BD5DCCE4019073E4@EXCHANGE.TIGR.ORG |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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: 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
From: | "Hoover, Jeffrey" <jhoover(at)jcvi(dot)org> |
---|---|
To: | "dgront" <dgront(at)chem(dot)uw(dot)edu(dot)pl>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Complicated GROUP BY |
Date: | 2008-07-11 19:04:34 |
Message-ID: | E92C2B1CB12A7A4683697273BD5DCCE4019073E6@EXCHANGE.TIGR.ORG |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | "Adam Rich" <adam(dot)r(at)sbcglobal(dot)net> |
---|---|
To: | "'dgront'" <dgront(at)chem(dot)uw(dot)edu(dot)pl>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Complicated GROUP BY |
Date: | 2008-07-11 19:50:32 |
Message-ID: | 031301c8e38f$61a0b650$24e222f0$@r@sbcglobal.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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?
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