Re: "Join" on delimeter aggregate query

On Sun, 8 Jun 2003, Tom Lane wrote:

> Eivind Kvedalen <eivindkv(at)ifi(dot)uio(dot)no> writes:
> > SELECT a, concat(b) FROM (SELECT a,b FROM test ORDER BY a,b) T GROUP BY a;
>
> > The ORDER BY is included to sort the rows before they are aggregated. I'm
> > not sure that this guarantees that they actually will be sorted, but maybe
> > some of the postgresql hackers can confirm/deny this?
>
> This technique will work reliably as of 7.4, but it's not reliable
> in existing releases. The GROUP BY will do its own sort on A, and
> unless qsort() is stable on your machine (which it's not, in most
> implementations) the secondary ordering by B will be destroyed.
>
> The fix in 7.4 simply makes the planner smart enough to notice that
> the sub-select's output is already adequately sorted for grouping
> by A.

Ok. What I actually had in mind was whether the optimizer would remove the
ORDER BY clause completely or not, as it isn't used in the top-level
SELECT query, and SQL doesn't in general guarantee ordered rows back
unless there's an ORDER BY in the top-level SELECT (I haven't read the SQL
standard, so I might very well be wrong here).

The GROUP BY sorts on A to do the grouping correctly, right? (That is,
removing the duplicates from A)

(Now, thinking more about this, removing the ORDER BY in the optimizer as
I suggested above would effectively remove the ORDER BY in created views,
right?)

Eivind

--

| Mail: eivindkv(at)ifi(dot)uio(dot)no | Lazy on IRC
| HP: www.stud.ifi.uio.no/~eivindkv | "Jeg skal vrenge deg med håret
| Tlf: 22187123/93249534 | inn."
| | -- Yang Tse Lyse