Re: Preserving order through an inner join

Hi Pavel,

I'm not really interested in a "my database is better than your database" discussion.

I want to know how to preserve the order. As I said, moving the ORDER BY out of the subquery would be a lot of work.

I am trying to find another solution.

Any suggestions from anyone?

Kevin

--- On Sun, 9/26/10, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> 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:23 PM
> 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
> >
>