pgsql aggregate: conditional max - Mailing list pgsql-sql

Hi,

I need a special aggregation function. For instance, given the following 
table data:
  aid    |   cat   | weight
----------+---------+---------a1  | Drama   |       1a1  | Romance |       6a1  | Short   |       1a1 | Other   |
7a2 | Comedy  |       1a2 | Drama   |       2a3  | Drama   |       1a3 | Adult   |       2a3 | Comedy  |       1a3 |
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