Re: Preserving order through an inner join

Hello

2010/9/26 Kevin Jardine <kevinjardine(at)yahoo(dot)com>:
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>