Thread: Order By for aggregate functions (Simulating Group_concat)

Hi All

I'm trying to create a aggregate function similar 9but not identical) to
mysql's group_concat.

What I want to be able to do is pass and order by field to the aggregate so
I can be certain I get the list of strings in the correct order.

Does anyone have any ideas how this could be done? In a previous thread on
aggregates a couple of people said that they thought it was doable but
nobody hinted at how.

Thanks in advance

Charlotte Pollock

On Thu, Sep 01, 2005 at 10:04:12AM +0100, Charlotte Pollock wrote:
> Hi All
>
> I'm trying to create a aggregate function similar 9but not identical) to
> mysql's group_concat.
>
> What I want to be able to do is pass and order by field to the aggregate so
> I can be certain I get the list of strings in the correct order.

Order them before the aggregate?

SELECT aggregate(field) FROM (SELECT field FROM xxx ORDER BY wherever) x;

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Charlotte Pollock <c.pollock@bangor.ac.uk> writes:
> I'm trying to create a aggregate function similar 9but not identical) to
> mysql's group_concat.

> What I want to be able to do is pass and order by field to the aggregate so
> I can be certain I get the list of strings in the correct order.

The way this is usually done in PG is to order the data before it gets
to the aggregate function.  For the ungrouped case this is easy:

    SELECT my_concat(foo) FROM
      (SELECT foo FROM ... ORDER BY something) ss;

If you're trying to aggregate within groups it's a bit trickier.  The
secret is that the ordering of the inner sub-select has to match the
outer GROUP BY:

    SELECT my_concat(foo), bar FROM
      (SELECT foo,bar FROM ... ORDER BY bar, something) ss
    GROUP BY bar;

In some cases it'll still work with just ORDER BY something, but that
depends on which plan type the planner happens to choose, so it's not
reliable to leave off the ORDER BY bar.

This requires a fairly recent PG ... I think we fixed the planner to
make this work properly in 7.4.

            regards, tom lane

On Thu, Sep 01, 2005 at 05:14:41PM +0200, Martijn van Oosterhout wrote:
> On Thu, Sep 01, 2005 at 10:04:12AM +0100, Charlotte Pollock wrote:
> > What I want to be able to do is pass and order by field to the aggregate so
> > I can be certain I get the list of strings in the correct order.
>
> Order them before the aggregate?
>
> SELECT aggregate(field) FROM (SELECT field FROM xxx ORDER BY wherever) x;

I've occasionally relied on this but I've never been completely
comfortable with it.  Is there any guarantee that the subquery's
ordering will be maintained as rows are fed to the aggregate, or
is that just an accident of the current implementation?

--
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:
> On Thu, Sep 01, 2005 at 05:14:41PM +0200, Martijn van Oosterhout wrote:
>> SELECT aggregate(field) FROM (SELECT field FROM xxx ORDER BY wherever) x;

> I've occasionally relied on this but I've never been completely
> comfortable with it.  Is there any guarantee that the subquery's
> ordering will be maintained as rows are fed to the aggregate, or
> is that just an accident of the current implementation?

Well, it's not required by the SQL spec (in fact I believe ORDER BY
inside a subselect isn't even legal per the SQL spec) ... but we do
promise it in the current implementation and I doubt we'd break the
promise in future, because it is a mighty handy behavior for
user-defined aggregates.

            regards, tom lane