Date: Sun, 12 Mar 2006 09:05:37 -0500
From: Daniel CAUNE <d.caune@free.fr>
Subject: Re: pgsql aggregate: conditional max
In-reply-to: <4413AD1C.9050604@indiana.edu>
To: 'Weimao Ke' <wke@indiana.edu>, pgsql-sql@postgresql.org
Message-id: <0IW0005WSPTDIA40@VL-MH-MR002.ip.videotron.ca>
MIME-version: 1.0
Content-type: text/plain; charset=utf-8
Content-transfer-encoding: 7BIT
Thread-index: AcZFk0b6FyArL1I0QPGWvB402QEinQASR5Bw

> 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