On Fri, Apr 15, 2005 at 12:23:14AM -0400, Joseph Shraibman wrote: > > > Alvaro Herrera wrote: > > >>Incidentally when I did that I only got back one row. What's up with > >>that? > > > > > >Try with "union all" instead of plain union. > > > Talk about serendipity. The problem I've been struggling with for the > last few hours has been why my query wasn't producing sorted output even > though I put in an ORDER BY and the EXPLAIN shows that it is ordering. > The DISTINCT implied by the UNION must have been messing up the sorting. > > The docs say > (http://www.postgresql.org/docs/7.4/interactive/sql-select.html#SQL-UNION): > > select_statement is any SELECT statement without an ORDER BY, LIMIT, or > FOR UPDATE clause. (ORDER BY and LIMIT can be attached to a > subexpression if it is enclosed in parentheses. > > > > ... but I *did* put my SELECTs in parentheses. This is either a bug in > pg or a serious ommision from the docs. It's not a bug, though it could possibly made clearer in the docs. If you want your final output in a specific order, you have to put you ORDER BY in the very outermost level of the query. PostgreSQL is pretty liberal about where you can put ORDER BY, but when it comes to output ordering only the order of the final query step matters. Why allow ordering elsewhere? Consider this more-performant replacement for SELECT max(blah): SELECT max FROM (SELECT blah FROM table ORDER BY blah DESC LIMIT 1) a ; Maybe not a great example since you'll only get one row back, but the point is that the ORDER BY in the subquery doesn't mean a thing when it comes to output order. -- Jim C. Nasby, Database Consultant decibel@xxxxxxxxxxx Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
