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
WHERE table.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. The query I wrote retrieves all the categories that have the maximum weight, but perhaps you just want one per aid.

--
Daniel