Re: pgsql aggregate: conditional max

> 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! :)
> 

SELECT aid, cat FROM table, (   SELECT aid, max(weight) as weight     FROM table     GROUP BY aid) AS tablemaxweight
WHEREtable.aid = tablemaxweight.aid   AND table.weight = tablemaxweight.aid;
 

There is a limit case you don't specify how to deal with, when two or more categories have the same maximum weight.
Thequery I wrote retrieves all the categories that have the maximum weight, but perhaps you just want one per aid.
 

--
Daniel