Hi Pavel, I'm not really interested in a "my database is better than your database" discussion. I want to know how to preserve the order. As I said, moving the ORDER BY out of the subquery would be a lot of work. I am trying to find another solution. Any suggestions from anyone? Kevin --- On Sun, 9/26/10, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote: > From: Pavel Stehule <pavel.stehule@xxxxxxxxx> > Subject: Re: Preserving order through an inner join > To: "Kevin Jardine" <kevinjardine@xxxxxxxxx> > Cc: pgsql-general@xxxxxxxxxxxxxx > Date: Sunday, September 26, 2010, 9:23 PM > Hello > > 2010/9/26 Kevin Jardine <kevinjardine@xxxxxxxxx>: > > 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. > > > > you can block a hash join that cannot to protect order. But > it can be > very contra-productive - this method of JOIN is the most > faster. > SQLite or MySQL are too simple and doesn't provide some > more > sophisticated constructs. Principally your query is wrong - > the real > order is based on final ORDER BY. It's probable, so your > queries will > stop working (on SQLite or MySQL) in future, when these > databases will > be more mature. > > Regards > > Pavel Stehule > > > 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 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. > > > > Any suggestions for getting this to work? > > > > Kevin > > > > > > > > > > -- > > 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
