From: | Kevin Jardine <kevinjardine(at)yahoo(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Preserving order through an inner join |
Date: | 2010-09-26 19:59:23 |
Message-ID: | 453197.63996.qm@web52901.mail.re2.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
OK, then.
The sub-select needs to go.
Thanks for helpful advice.
Kevin
--- On Sun, 9/26/10, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Subject: Re: [GENERAL] Preserving order through an inner join
> To: "Kevin Jardine" <kevinjardine(at)yahoo(dot)com>
> Cc: pgsql-general(at)postgresql(dot)org
> Date: Sunday, September 26, 2010, 9:37 PM
> 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
>
> --
> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Karsten Hilbert | 2010-09-26 20:06:28 | Re: Preserving order through an inner join |
Previous Message | Tom Lane | 2010-09-26 19:37:06 | Re: Preserving order through an inner join |