"Sam Mason" <[EMAIL PROTECTED]> writes: > On Tue, Mar 25, 2008 at 06:58:06PM +0000, Gregory Stark wrote: > The main thing I wanted to avoid was an explosion of sub-queries that > you get with DISTINCT ON style queries. For example, with record style > syntax, I can do: > > SELECT i, (MIN((j,k))).k AS ka, (MIN((mycode(j),k))).k AS kb > FROM tbl > GROUP BY i; > > whereas using DISTINCT ON I'd have to do: ... > Which gets unmanageable quickly. Any idea how window functions would > cope with this sort of complexity? Or is this what you meant by: > >> I suspect it will look more like the DISTINCT ON solution than the >> min(record) >> solution.
The flip side is that if you want to get several fields based on min(j) the min(record) approach requires you to write that expression several times (and the database to calculate it several times). I think the window functions might (assuming an ideal implementation) get the best of both worlds. You would be able to do something with multiple partitions so you could ask of a few columns where rank over j = 1 and a few more columns where rank over k = 1. But, uh, I'm not sure. I'll have to sit down with the spec and see if that's true. Furthermore it may be wishful thinking to hope that the implementation will do anything special with the special case where you're only selecting records where rank = 1. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
