Re: ORDER BY does not work as expected with multiple joins

Tom Lane wrote:
> Adam Rosi-Kessel <adam@rosi-kessel.org> writes:
>> I have a multijoin SELECT statement where the ORDER BY clause doesn't
>> seem to be working.
> You haven't mentioned your Postgres version, nor provided anywhere near
> enough information to let other people reproduce the problem.  You might
> find it helpful to read this:
> http://www.postgresql.org/docs/8.1/static/bug-reporting.html

Thanks. I didn't think this was a bug--I thought I had an error in my SELECT
statement somehow. I certainly would have included more information with a
bug report. (For the record, it is PostgreSQL 7.4 from Debian Sarge).

> One comment is that your joining seems wrong: I'd have thought the
> RIGHT JOIN should be LEFT JOIN for what you're trying to do.  But that
> shouldn't make any difference to whether the sort order is correct.

Actually, it looks like I might have just been confused about DATESTYLE.
Adding in some dates where the day was greater than 12 clarified how the
sorting was being done. Due to a bug in my program, the DATESTYLE was set
differently for this multiple JOIN SELECT. Sorry about the misguided request!

But I am curious why you think a LEFT JOIN should be used rather than RIGHT
JOIN? I think I believe I am getting the desired set of records. When I
substitute LEFT JOIN for RIGHT JOIN I actually get the same result. Can you
help me understand under what conditions, in this case, I would obtain a
different result?

Adam