Re: Preserving order through an inner join

> -----Original Message-----
> From: Kevin Jardine [mailto:kevinjardine(at)yahoo(dot)com]
> Sent: Sunday, September 26, 2010 3:15 PM
> To: pgsql-general(at)postgresql(dot)org
> 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