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 weig
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 mor
Michael Fuhr wrote:
SELECT DISTINCT ON (aid) aid, cat
FROM tablename
ORDER BY aid, weight DESC, cat;
Good pointer. I think this will solve my problem. :)
SELECT aid, cat
FROM tablename AS t
JOIN (SELECT aid, max(weight) AS weight
FROM tablename
GROUP BY aid) AS s USING (aid, weig
Michael Fuhr wrote:
On Sun, Mar 12, 2006 at 12:34:57AM -0500, Jeffrey Melloy wrote:
Should be able to do this with a standard max() aggregate.
select aid, cat, max(weight)
from table
group by aid, cat;
That query returns the maximum weight for each (aid, cat) pair.
Against the exampl
> 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
On Sun, Mar 12, 2006 at 12:34:57AM -0500, Jeffrey Melloy wrote:
> Should be able to do this with a standard max() aggregate.
>
> select aid, cat, max(weight)
> from table
> group by aid, cat;
That query returns the maximum weight for each (aid, cat) pair.
Against the example data it returns the e
On Sun, Mar 12, 2006 at 12:09:48AM -0500, Weimao Ke wrote:
> 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
PostgreSQL has a non-standard DI
Weimao Ke wrote:
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 | Dram
