From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Kevin Jardine <kevinjardine(at)yahoo(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Preserving order through an inner join |
Date: | 2010-09-26 19:37:06 |
Message-ID: | 15888.1285529826@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Kevin Jardine <kevinjardine(at)yahoo(dot)com> writes:
> 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.
Indeed. Many of the possible join techniques won't preserve that ordering.
> This works for other databases (eg. MySQL and Sqllite3) but not PostgreSQL.
It might sometimes accidentally fail to fail, but I think you'll find
that there are *no* SQL databases where this is guaranteed to work the
way you expect. The SQL standard explicitly disavows any particular
output row order unless there is a top-level ORDER BY. (In fact,
unless things have changed recently an ORDER BY in a sub-select isn't
even legal per spec.)
> 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.
Nope, that's what you need to do.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Jardine | 2010-09-26 19:59:23 | Re: Preserving order through an inner join |
Previous Message | Kevin Jardine | 2010-09-26 19:32:01 | Re: Preserving order through an inner join |