Re: [GENERAL] Top N within groups?
Martijn van Oosterhout wrote:
On Fri, Jul 11, 2008 at 01:24:28PM +1000, Klint Gore wrote:
> [thinking out loud]
> Can someone familiar with the source for DISTINCT ON comment on how hard
> it would be to add another parameter to return more than one row?
From a programming point of view, it wouldn't be too hard. However,
deciding what syntax to use is a another question entirely. I think
your suggestion of FOR isn't good (ambiguous syntax, 'for' could be a
column name), but I can't think of a better one.
'for' is a reserved key word so can't be a column name.
http://www.postgresql.org/docs/8.3/interactive/sql-keywords-appendix.html
postgres=# create table foo (for int);
ERROR: syntax error at or near "for"
The worst I think you could get would be
select distinct on ("for") for 4 "for" from table4 for update;
but even then, I think the parser could work out what you want.
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Top N within groups?
On Fri, Jul 11, 2008 at 01:24:28PM +1000, Klint Gore wrote: > Can someone familiar with the source for DISTINCT ON comment on how hard > it would be to add another parameter to return more than one row? you can make top-n per group quite easily using the technique described here: http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/ best regards, depesz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Top N within groups?
On Fri, Jul 11, 2008 at 01:24:28PM +1000, Klint Gore wrote: > [thinking out loud] > Can someone familiar with the source for DISTINCT ON comment on how hard > it would be to add another parameter to return more than one row? From a programming point of view, it wouldn't be too hard. However, deciding what syntax to use is a another question entirely. I think your suggestion of FOR isn't good (ambiguous syntax, 'for' could be a column name), but I can't think of a better one. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
[GENERAL] Top N within groups?
[was {SOLVED?] Re: [GENERAL] functional index not used, looping simpler
query just faster]
Ivan Sergio Borgonovo wrote:
I'm still curious to know if this could be done
efficiently with just one query.
[thinking out loud]
Can someone familiar with the source for DISTINCT ON comment on how hard
it would be to add another parameter to return more than one row?
e.g.
To do TOP 1 within an unknown number of groups
select distinct on (groupid) groupid, identifier, count(*)
from somequery
group by groupid, identifier
order by 1,3 desc,2;
I'm thinking that, for the top 3 within each group, something like
select distinct on (groupid) FOR 3 groupid, identifier, count(*)
from somequery
group by groupid, identifier
order by 1,3 desc,2;
For Ivan's case, groupid = brand, identifer = item. The where clause
applies the date limits.
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
