Re: pgsql aggregate: conditional max

Daniel CAUNE wrote:

> 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.
>
>
Yes, this will introduce duplicates. Yet it is not too difficult to
select only one for each aid from the results. Thank you!

Weimao

>--
>Daniel
>
>
>
>

--
All best,

Weimao

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