Preserving order through an inner join

From: Kevin Jardine <kevinjardine(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Preserving order through an inner join
Date: 2010-09-26 19:14:49
Message-ID: 551808.43868.qm@web52908.mail.re2.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2010-09-26 19:23:07 Re: Preserving order through an inner join
Previous Message Oleg Bartunov 2010-09-26 18:14:41 Re: Visualize GiST Index