On Sun, 8 Jun 2003, Tom Lane wrote: > Eivind Kvedalen <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] | Lazy on IRC | HP: www.stud.ifi.uio.no/~eivindkv | "Jeg skal vrenge deg med håret | Tlf: 22187123/93249534 | inn." | | -- Yang Tse Lyse ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
