pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql aggregate: conditional max
9+ messages / 5 participants
[nested] [flat]
* pgsql aggregate: conditional max
@ 2006-03-12 05:09 Weimao Ke <wke@indiana.edu>
0 siblings, 3 replies; 9+ messages in thread
From: Weimao Ke @ 2006-03-12 05:09 UTC (permalink / raw)
To: pgsql-sql
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 | Drama | 2
a3 | Drama | 1
a3 | Adult | 2
a3 | Comedy | 1
a3 | 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pgsql aggregate: conditional max
@ 2006-03-12 05:34 Jeffrey Melloy <jmelloy@visualdistortion.org>
parent: Weimao Ke <wke@indiana.edu>
2 siblings, 1 reply; 9+ messages in thread
From: Jeffrey Melloy @ 2006-03-12 05:34 UTC (permalink / raw)
To: Weimao Ke <wke@indiana.edu>; +Cc: pgsql-sql
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 | Drama | 2
> a3 | Drama | 1
> a3 | Adult | 2
> a3 | Comedy | 1
> a3 | 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! :)
>
Should be able to do this with a standard max() aggregate.
select aid, cat, max(weight)
from table
group by aid, cat;
Jeff
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pgsql aggregate: conditional max
@ 2006-03-12 05:42 Michael Fuhr <mike@fuhr.org>
parent: Weimao Ke <wke@indiana.edu>
2 siblings, 1 reply; 9+ messages in thread
From: Michael Fuhr @ 2006-03-12 05:42 UTC (permalink / raw)
To: Weimao Ke <wke@indiana.edu>; +Cc: pgsql-sql
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 DISTINCT ON clause that would work.
See the weather_reports example in the documentation for SELECT:
http://www.postgresql.org/docs/8.1/interactive/sql-select.html
Try this query against your example data:
SELECT DISTINCT ON (aid) aid, cat
FROM tablename
ORDER BY aid, weight DESC, cat;
If multiple rows for a given aid match that aid's max weight then
the above query will return the first matching row according to the
given sort order.
Some people object to DISTINCT ON because it's non-deterministic if
you don't order by enough columns. Here's something more standard;
it'll return all rows that match a given aid's max weight:
SELECT aid, cat
FROM tablename AS t
JOIN (SELECT aid, max(weight) AS weight
FROM tablename
GROUP BY aid) AS s USING (aid, weight);
--
Michael Fuhr
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pgsql aggregate: conditional max
@ 2006-03-12 05:48 Michael Fuhr <mike@fuhr.org>
parent: Jeffrey Melloy <jmelloy@visualdistortion.org>
0 siblings, 1 reply; 9+ messages in thread
From: Michael Fuhr @ 2006-03-12 05:48 UTC (permalink / raw)
To: Jeffrey Melloy <jmelloy@visualdistortion.org>; +Cc: Weimao Ke <wke@indiana.edu>; pgsql-sql
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 entire table, not the
(aid, cat) pair with the max weight for a given aid.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pgsql aggregate: conditional max
@ 2006-03-12 14:05 Daniel CAUNE <d.caune@free.fr>
parent: Weimao Ke <wke@indiana.edu>
2 siblings, 1 reply; 9+ messages in thread
From: Daniel CAUNE @ 2006-03-12 14:05 UTC (permalink / raw)
To: Weimao Ke <wke@indiana.edu>; pgsql-sql
> 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 | Drama | 2
> a3 | Drama | 1
> a3 | Adult | 2
> a3 | Comedy | 1
> a3 | 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! :)
>
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.
--
Daniel
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pgsql aggregate: conditional max
@ 2006-03-12 16:55 Weimao Ke <wke@indiana.edu>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 9+ messages in thread
From: Weimao Ke @ 2006-03-12 16:55 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Jeffrey Melloy <jmelloy@visualdistortion.org>; pgsql-sql
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 example data it returns the entire table, not the
>(aid, cat) pair with the max weight for a given aid.
>
>
>
Michael is right. This query does not solve the problem...
--
All best,
Weimao
Weimao Ke
Indiana University Bloomington
School of Library and Information Science
http://ella.slis.indiana.edu/~wke
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pgsql aggregate: conditional max
@ 2006-03-12 17:07 Weimao Ke <wke@indiana.edu>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 9+ messages in thread
From: Weimao Ke @ 2006-03-12 17:07 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
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, 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...:)
I really appreciate your help!
--
All best,
Weimao
Weimao Ke
Indiana University Bloomington
School of Library and Information Science
http://ella.slis.indiana.edu/~wke
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pgsql aggregate: conditional max
@ 2006-03-12 19:59 Weimao Ke <wke@indiana.edu>
parent: Daniel CAUNE <d.caune@free.fr>
0 siblings, 0 replies; 9+ messages in thread
From: Weimao Ke @ 2006-03-12 19:59 UTC (permalink / raw)
To: Daniel CAUNE <d.caune@free.fr>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pgsql aggregate: conditional max
@ 2006-03-27 14:14 Markus Schaber <schabi@logix-tt.com>
parent: Weimao Ke <wke@indiana.edu>
0 siblings, 0 replies; 9+ messages in thread
From: Markus Schaber @ 2006-03-27 14:14 UTC (permalink / raw)
To: Weimao Ke <wke@indiana.edu>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2006-03-27 14:14 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-12 05:09 pgsql aggregate: conditional max Weimao Ke <wke@indiana.edu>
2006-03-12 05:34 ` Jeffrey Melloy <jmelloy@visualdistortion.org>
2006-03-12 05:48 ` Michael Fuhr <mike@fuhr.org>
2006-03-12 16:55 ` Weimao Ke <wke@indiana.edu>
2006-03-12 05:42 ` Michael Fuhr <mike@fuhr.org>
2006-03-12 17:07 ` Weimao Ke <wke@indiana.edu>
2006-03-27 14:14 ` Markus Schaber <schabi@logix-tt.com>
2006-03-12 14:05 ` Daniel CAUNE <d.caune@free.fr>
2006-03-12 19:59 ` Weimao Ke <wke@indiana.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox