OK, then. The sub-select needs to go. Thanks for helpful advice. Kevin --- On Sun, 9/26/10, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > From: Tom Lane <tgl@xxxxxxxxxxxxx> > Subject: Re: Preserving order through an inner join > To: "Kevin Jardine" <kevinjardine@xxxxxxxxx> > Cc: pgsql-general@xxxxxxxxxxxxxx > Date: Sunday, September 26, 2010, 9:37 PM > Kevin Jardine <kevinjardine@xxxxxxxxx> > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
