Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f4EJQYA45856 for ; Mon, 14 May 2001 15:26:34 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from mail10.burlee.com (mail10.burlee.com [199.93.70.130]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4EEjEA16214 for ; Mon, 14 May 2001 10:45:14 -0400 (EDT) (envelope-from ddkilzer@lubricants-oil.com) Received: from marge.lubricants-oil.com [63.227.79.185] by mail10.burlee.com with ESMTP (SMTPD32-6.05) id AFCE5AB700D0; Mon, 14 May 2001 10:46:38 -0400 Received: from elbonia.lubricants-oil.com (elbonia.lubricants-oil.com [192.168.168.9]) by marge.lubricants-oil.com (8.9.3/8.9.3/Debian 8.9.3-21) with ESMTP id JAA23406; Mon, 14 May 2001 09:44:52 -0500 Received: (from ddkilzer@localhost) by elbonia.lubricants-oil.com (8.9.3/8.9.3) id JAA18121; Mon, 14 May 2001 09:42:16 -0500 From: "David D. Kilzer" Date: Mon, 14 May 2001 09:42:15 -0500 To: pgsql-sql@postgresql.org Cc: Tom Lane Subject: Re: Using ORDER BY with AGGREGATE/GROUP BY in a SELECT statement Message-ID: <20010514094215.C17879@elbonia.lubricants-oil.com> References: <20010507122759.B12656@elbonia.lubricants-oil.com> <5443.989598832@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.3.15i In-Reply-To: <5443.989598832@sss.pgh.pa.us>; from tgl@sss.pgh.pa.us on Fri, May 11, 2001 at 12:33:52PM -0400 X-Archive-Number: 200105/131 X-Sequence-Number: 2947 Tom, Thanks for the reply, and for correcting my broken SQL. :^) A couple days after I sent my own follow-up message, I realized how silly my question was. I'll let future questions sit a bit longer next time. To make up for my silly question, here is a function + aggregate I created while working on a different aspect of my original problem. Perhaps someone will find this useful. The function, 'booland', returns the logical 'AND' of two input values. DROP FUNCTION "booland" (bool, bool); CREATE FUNCTION "booland" (bool, bool) RETURNS bool AS 'BEGIN RETURN $1 AND $2; END;' LANGUAGE 'PLPGSQL'; The aggregate, 'logical_and', returns the logical 'AND' of all values in a column in an aggregated query. DROP AGGREGATE logical_and bool; CREATE AGGREGATE logical_and ( SFUNC1 = booland, BASETYPE = bool, STYPE1 = bool, INITCOND1 = 't' ); Obviously, this concept could be extended for a logical 'OR' function and aggregate. Dave On Fri, May 11, 2001 at 12:33:52PM -0400, Tom Lane wrote: > "David D. Kilzer" writes: > > [ wants to write an aggregate function that returns its last input ] > > The SQL model of query processing has a very definite view of the stages > of processing: first group by, then aggregate, and last order by. Tuple > ordering is irrelevant according to the basic semantics of the language. > Probably the SQL authors would have left out ORDER BY entirely if they > could have got away with it, but instead they made it a vestigial > appendage that is only allowed at the very last instant before query > outputs are forwarded to a client application. > > Thus, it is very bad form to write an aggregate that depends on the > order it sees its inputs in. This won't be changed, because it's part > of the nature of the language. > > In PG 7.1 it's possible to hack around this by ordering the result of > a subselect-in-FROM: > > SELECT orderedagg(ss.x) FROM (select x from tab order by y) ss; > > which is a gross violation of the letter and spirit of the spec, and > should not be expected to be portable to other DBMSes; but it gets the > job done if you are intent on writing an ordering-dependent aggregate. > > However, I don't see any good way to combine this with grouping, since > if you apply GROUP BY to the output of the subselect you'll lose the > ordering again. > > > SELECT r.personid AS personid > > ,SUM(r.laps) AS laps > > ,COUNT(DISTINCT r.id) AS nightsraced > > ,(SELECT r.carid > > FROM race r > > WHERE r.personid = 14 > > ORDER BY r.date DESC > > LIMIT 1) AS carid > > FROM race r > > WHERE r.personid = 14 > > GROUP BY r.personid > > ORDER BY r.date; > > This is likely to be reasonably efficient, actually, since the subselect > will be evaluated only once per output group --- in fact, as you've > written it it'll only be evaluated once, period, since it has no > dependencies on the outer query. More usually you'd probably do > > ,(SELECT r2.carid > FROM race r2 > WHERE r2.personid = r.personid > ORDER BY r2.date DESC > LIMIT 1) AS carid > > so that the result tracks the outer query, and in this form it'd be > redone once per output row. > > regards, tom lane