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 13E079DC83C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 12 Mar 2006 01:09:55 -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 24526-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 12 Mar 2006 01:09:53 -0400 (AST)
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id 806079DC833
	for <pgsql-sql@postgresql.org>; Sun, 12 Mar 2006 01:09:52 -0400 (AST)
Received: from rockridge.uits.indiana.edu (rockridge.uits.indiana.edu
	[129.79.1.74])
	by svr4.postgresql.org (Postfix) with ESMTP id 657135AF0D0
	for <pgsql-sql@postgresql.org>; Sun, 12 Mar 2006 05:09:52 +0000 (GMT)
Received: from mail-relay.iu.edu (stjoseph.uits.indiana.edu [129.79.1.78])
	by rockridge.uits.indiana.edu (8.12.10/8.12.10/IUPO) with ESMTP id
	k2C59nEU021644
	for <pgsql-sql@postgresql.org>; Sun, 12 Mar 2006 00:09:49 -0500 (EST)
Received: from [192.168.0.3] (149-159-1-54.dhcp-bl.indiana.edu [149.159.1.54])
	(authenticated bits=0)
	by mail-relay.iu.edu (8.12.10/8.12.10/IUPO) with ESMTP id
	k2C59m7r014156
	for <pgsql-sql@postgresql.org>; Sun, 12 Mar 2006 00:09:49 -0500 (EST)
Message-ID: <4413AD1C.9050604@indiana.edu>
Date: Sun, 12 Mar 2006 00:09:48 -0500
From: Weimao Ke <wke@indiana.edu>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: pgsql aggregate: conditional max
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.479 required=5 tests=[DNS_FROM_RFC_ABUSE=0.479]
X-Spam-Score: 0.479
X-Spam-Level: 
X-Archive-Number: 200603/168
X-Sequence-Number: 24514

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

-- 
All best, 

Weimao

Weimao Ke
Indiana University Bloomington
School of Library and Information Science
http://ella.slis.indiana.edu/~wke