From: Jeffrey Melloy <jmelloy@visualdistortion.org>
To: Weimao Ke <wke@indiana.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: pgsql aggregate: conditional max
Date: Sun, 12 Mar 2006 00:34:57 -0500
Message-ID: <4413B301.7070207@visualdistortion.org> (raw)
In-Reply-To: <4413AD1C.9050604@indiana.edu>
References: <4413AD1C.9050604@indiana.edu>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jmelloy@visualdistortion.org, wke@indiana.edu
Subject: Re: pgsql aggregate: conditional max
In-Reply-To: <4413B301.7070207@visualdistortion.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox