Following up on other replies, and agreeing that SQL has no inherent row
ordering and it is only by accident that you are seeing such ...
The *only* way to reliably preserve sort order is by explicitly encoding the
order in your data, by maintaining a column or columns through to the outermost
query on which you sort.
A generic solution which you can apply is to use a SQL "RANK"-alike feature to
generate a serial number column in your rowset; you can do this in the inner
query where you want to have all your unique or complex sorting details, and
then your sort order will be preserved (as long as you explicitly propagate said
column either by name or with *) through to the outer query. You can then order
by and exclude said column in the outermost query if you want, but you don't
have to because your application will still get the ordering information in said
extra column.
For example, something like this:
SELECT stuff FROM
(SELECT more stuff, rank() OVER (ORDER BY field1) AS ordcol FROM
table1
) AS q1
INNER JOIN table2 ON ( ... )
ORDER BY ordcol
See
http://www.postgresql.org/docs/9.0/interactive/queries-table-expressions.html#QUERIES-WINDOW
and the 3 urls it links to.
-- Darren Duncan
Kevin Jardine wrote:
> 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.
>
> This works for other databases (eg. MySQL and Sqllite3) but not PostgreSQL.
>
> I would really like to support PostgreSQL but this ordering problem is stopping me from doing so.
>
> I can make some small changes to the query structure as long as it works for the other DBs as well. Moving the ORDER
BYoutside q1 would be a large amount of work, however (these queries are generated by a program), so I am hoping that
thereis a simpler solution.
>
> Any suggestions for getting this to work?