> -----Original Message----- > From: Kevin Jardine [mailto:kevinjardine@xxxxxxxxx] > Sent: Sunday, September 26, 2010 3:15 PM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: Preserving order through an inner join > > 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 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 > As others stated, it's a bad idea to order sub-select. But if you really, really need it (m.b. not in this particular case), you could do: SELECT stuff FROM (SELECT field1, field2, COUNT(*) FROM table1 GROUP BY field1, field2 ORDER BY field1) AS q1 INNER JOIN table2 ON ( ... ) Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
