X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id C5FB39DCB79
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 12 Mar 2006 01:33:19 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 25705-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 12 Mar 2006 01:33:18 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from calvin.slamb.org (calvin.slamb.org [216.136.66.56])
	by postgresql.org (Postfix) with ESMTP id C0B369DCA94
	for <pgsql-sql@postgresql.org>; Sun, 12 Mar 2006 01:33:17 -0400 (AST)
Received: by calvin.slamb.org (Postfix, from userid 103)
	id 60CA570036; Sat, 11 Mar 2006 23:33:17 -0600 (CST)
Received: from [192.168.0.100] (host-12-179-64-224.nctv.com [12.179.64.224])
	(using TLSv1 with cipher RC4-MD5 (128/128 bits))
	(No client certificate requested)
	by calvin.slamb.org (Postfix) with ESMTP
	id ACACB6FDC3; Sat, 11 Mar 2006 23:33:14 -0600 (CST)
Message-ID: <4413B301.7070207@visualdistortion.org>
Date: Sun, 12 Mar 2006 00:34:57 -0500
From: Jeffrey Melloy <jmelloy@visualdistortion.org>
User-Agent: Thunderbird 1.5 (X11/20051201)
MIME-Version: 1.0
To: Weimao Ke <wke@indiana.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: pgsql aggregate: conditional max
References: <4413AD1C.9050604@indiana.edu>
In-Reply-To: <4413AD1C.9050604@indiana.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200603/169
X-Sequence-Number: 24515

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