Обсуждение: 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

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

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] 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

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] 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