Re: pgsql aggregate: conditional max

Weimao Ke wrote:
> Hi,
>
> I need a special aggregation function. For instance, given the 
> following table data:
>
>   aid    |   cat   | weight
> ----------+---------+---------
> a1  | Drama   |       1
> a1  | Romance |       6
> a1  | Short   |       1
> a1 | Other   |       7
> a2  | Comedy  |       1
> a2 | Drama   |       2
> a3  | Drama   |       1
> a3 | Adult   |       2
> a3 | Comedy  |       1
> a3 | Other   |       1
>
> I want to group by "aid" and choose the category (i.e., "cat") with 
> the largest "weight":
>
> aid   |   max_weighted_cat
> ----+---------------------
> a1   |   Other
> a2   |   Drama
> a3   |   Adult
>
> Any ideas? Thank you! :)
>

Should be able to do this with a standard max() aggregate.

select aid, cat, max(weight)
from table
group by aid, cat;

Jeff