pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David D. Kilzer <ddkilzer@lubricants-oil.com>
To: pgsql-sql@postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Using ORDER BY with AGGREGATE/GROUP BY in a SELECT statement
Date: Mon, 14 May 2001 09:42:15 -0500
Message-ID: <20010514094215.C17879@elbonia.lubricants-oil.com> (raw)
In-Reply-To: <5443.989598832@sss.pgh.pa.us>
References: <20010507122759.B12656@elbonia.lubricants-oil.com>
	<5443.989598832@sss.pgh.pa.us>

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" <ddkilzer@lubricants-oil.com> 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




view thread (4+ messages)

Message-ID: <20010514094215.C17879@elbonia.lubricants-oil.com>
Permalink:  ../20010514094215.C17879@elbonia.lubricants-oil.com/
Also on:    postgresql.org/message-id/20010514094215.C17879@elbonia.lubricants-oil.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: ddkilzer@lubricants-oil.com, tgl@sss.pgh.pa.us
  Subject: Re: Using ORDER BY with AGGREGATE/GROUP BY in a SELECT statement
  In-Reply-To: <20010514094215.C17879@elbonia.lubricants-oil.com>
  References: <20010507122759.B12656@elbonia.lubricants-oil.com> <5443.989598832@sss.pgh.pa.us> <20010514094215.C17879@elbonia.lubricants-oil.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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