X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 10F12D1CCA6 for ; Fri, 23 Jan 2004 02:24:06 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 44214-10 for ; Thu, 22 Jan 2004 22:23:38 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 041B3D1E868 for ; Thu, 22 Jan 2004 22:23:34 -0400 (AST) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id DD0ED3692E; Thu, 22 Jan 2004 21:23:36 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1Ajqyu-0000yu-00; Thu, 22 Jan 2004 21:23:36 -0500 To: Tom Lane Cc: Robert Creager , PGSQL Subject: Re: How can I get the last element out of GROUP BY sets? References: <20040118212834.7c667b9b.Robert_Creager@LogicalChaos.org> <20989.1074491070@sss.pgh.pa.us> In-Reply-To: <20989.1074491070@sss.pgh.pa.us> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 22 Jan 2004 21:23:36 -0500 Message-ID: <87y8rzxv13.fsf@stark.xeocode.com> Lines: 22 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/207 X-Sequence-Number: 16600 Tom Lane writes: > Robert Creager writes: > > ... one piece of data I need is the last value for each GROUP BY > > period. Alas, I cannot figure out how to do this. > > SELECT DISTINCT ON (rather than GROUP BY) could get this done for you. Or if you need to combine this with other aggregate functions like sum, count, etc: CREATE FUNCTION first_accum (integer, integer) RETURNS integer AS 'select coalesce($1,$2)' LANGUAGE sql; CREATE FUNCTION last_accum (integer, integer) RETURNS integer AS 'select $2' LANGUAGE sql; CREATE AGGREGATE first (BASETYPE = integer, SFUNC = first_accum, STYPE = integer); CREATE AGGREGATE last (BASETYPE = integer, SFUNC = last_accum, STYPE = integer); Then you can do first() and last(). These definitions only work for integer but you can pattern match for other datatypes. You might be able to get a universal function working using anyelement now, I haven't tried. -- greg