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

Reply via email to