Kevin Jardine <kevinjard...@yahoo.com> writes: > I have a query structured like this: > SELECT stuff FROM > (SELECT more stuff FROM > table1 > ORDER BY field1) AS q1 > INNER JOIN table2 ON ( ... )
> and have found that the INNER JOIN is ignoring the order set for q1. > The final results are not ordered by field1. Indeed. Many of the possible join techniques won't preserve that ordering. > This works for other databases (eg. MySQL and Sqllite3) but not PostgreSQL. It might sometimes accidentally fail to fail, but I think you'll find that there are *no* SQL databases where this is guaranteed to work the way you expect. The SQL standard explicitly disavows any particular output row order unless there is a top-level ORDER BY. (In fact, unless things have changed recently an ORDER BY in a sub-select isn't even legal per spec.) > I can make some small changes to the query structure as long as it works for > the other DBs as well. Moving the ORDER BY outside q1 would be a large amount > of work, however (these queries are generated by a program), so I am hoping > that there is a simpler solution. Nope, that's what you need to do. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general