From: | Jeffrey Melloy <jmelloy(at)visualdistortion(dot)org> |
---|---|
To: | Weimao Ke <wke(at)indiana(dot)edu> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: pgsql aggregate: conditional max |
Date: | 2006-03-12 05:34:57 |
Message-ID: | 4413B301.7070207@visualdistortion.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2006-03-12 05:42:12 | Re: pgsql aggregate: conditional max |
Previous Message | Weimao Ke | 2006-03-12 05:09:48 | pgsql aggregate: conditional max |