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

"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)  latest in thread

Message-ID: <5443.989598832@sss.pgh.pa.us>
Permalink:  ../5443.989598832@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/5443.989598832@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, ddkilzer@lubricants-oil.com
  Subject: Re: Using ORDER BY with AGGREGATE/GROUP BY in a SELECT statement
  In-Reply-To: <5443.989598832@sss.pgh.pa.us>
  References: <20010507122759.B12656@elbonia.lubricants-oil.com> <5443.989598832@sss.pgh.pa.us>

* 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