From: Daniel CAUNE <d.caune@free.fr>
To: Weimao Ke <wke@indiana.edu>
To: pgsql-sql@postgresql.org
Subject: Re: pgsql aggregate: conditional max
Date: Sun, 12 Mar 2006 09:05:37 -0500
Message-ID: <0IW0005WSPTDIA40@VL-MH-MR002.ip.videotron.ca> (raw)
In-Reply-To: <4413AD1C.9050604@indiana.edu>
> 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
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: d.caune@free.fr, wke@indiana.edu
Subject: Re: pgsql aggregate: conditional max
In-Reply-To: <0IW0005WSPTDIA40@VL-MH-MR002.ip.videotron.ca>
* 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