X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 05F639DC815 for ; Fri, 13 Jan 2006 11:14:45 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 24408-04 for ; Fri, 13 Jan 2006 11:14:46 -0400 (AST) X-Greylist: delayed 00:41:48.405478 by SQLgrey- Received: from bostoncoop.net (dsl001-169-236.bos1.dsl.speakeasy.net [72.1.169.236]) by postgresql.org (Postfix) with ESMTP id 8B55E9DC819 for ; Fri, 13 Jan 2006 11:14:39 -0400 (AST) Received: from [10.1.27.37] (metcalfe.wolfgreenfield.com [12.45.255.66]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by bostoncoop.net (Postfix) with ESMTP id ACE99A03E6; Fri, 13 Jan 2006 10:14:43 -0500 (EST) Message-ID: <43C7C3E2.1030205@rosi-kessel.org> Date: Fri, 13 Jan 2006 10:14:42 -0500 From: Adam Rosi-Kessel User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: Tom Lane Cc: pgsql-sql@postgresql.org Subject: Re: ORDER BY does not work as expected with multiple joins References: <43C7BA12.90103@rosi-kessel.org> <25011.1137164168@sss.pgh.pa.us> In-Reply-To: <25011.1137164168@sss.pgh.pa.us> X-Enigmail-Version: 0.93.2.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig923BF228234DAF5E807C8EB3" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200601/61 X-Sequence-Number: 23850 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig923BF228234DAF5E807C8EB3 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Tom Lane wrote: > Adam Rosi-Kessel 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 migh= t > 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 SEL= ECT 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 requ= est! But I am curious why you think a LEFT JOIN should be used rather than RIG= HT 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 y= ou help me understand under what conditions, in this case, I would obtain a different result? Adam --------------enig923BF228234DAF5E807C8EB3 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFDx8PidTf3ZklQ6qYRAk0ZAJ97lJ2o60sKJv35g1NbbYFi03G8ngCcDEhd 3Fyw2Ev+tUCq+Z2mnK4cySI= =uLmx -----END PGP SIGNATURE----- --------------enig923BF228234DAF5E807C8EB3--