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