Re: pgsql aggregate: conditional max

Hi, Weimao Ke,

Weimao Ke wrote:
>> SELECT aid, cat
>> FROM tablename AS t
>> JOIN (SELECT aid, max(weight) AS weight
>> FROM tablename
>> GROUP BY aid) AS s USING (aid, weight);
>>
> This query will return duplicates if there are multiple categories (for
> one aid) with the same max weight. Yet, I should be able to remove the
> duplicates somehow...:)

Try

SELECT DISTINCT aid, cat
FROM tablename AS t
JOIN (SELECT aid, max(weight) AS weight
FROM tablename
GROUP BY aid) AS s USING (aid, weight);

HTH,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org