From: Adam Rosi-Kessel <adam@rosi-kessel.org>
To: pgsql-sql@postgresql.org
Subject: ORDER BY does not work as expected with multiple joins
Date: Fri, 13 Jan 2006 09:32:50 -0500
Message-ID: <43C7BA12.90103@rosi-kessel.org> (raw)
I posted this message once through the Google Groups interface to pgsql.sql,
but I've received no replies and the message doesn't appear at all in the
MHonArc archives at postgresql.org, so I'm not sure it actually was
distributed to this list. Apologies if this is a duplicate:
I have a multijoin SELECT statement where the ORDER BY clause doesn't
seem to be working. I can't figure out how pgsql is sorting the
results, but it is definitely different from the same list without the
multiple joins.
To simplify: I have two tables. The first table ("table1") has three
fields: date, id1, and id2. The data look like this:
Date ID1 ID2
1/5/06 2 1
1/1/06 1 2
1/3/06 1 3
The second table ("table2") has two fields: id and name. The data look
like this:
ID NAME
1 John
2 Jane
3 Sam
What I am trying to do is join the two tables and sort by the date.
ID1 and ID2 in table1 *both* reference ID in table2. The desired
result of the SELECT statement would look like this:
Date name1 name2
1/1/06 John Jane
1/3/06 John Sam
1/5/06 Jane John
For some reason, the result is not properly sorting by date--the data
are being reordered, but not in a way I can understand.
Here is the SELECT statement:
SELECT date,x.name as name1,y.name AS name2 FROM table1 LEFT JOIN
table2 AS x ON id1=x.id RIGHT JOIN table2 AS y ON id2=y.id WHERE (date
IS NOT NULL AND (id1 = ? OR id2 = ?)) ORDER BY date;
(where ? is replaced by the desired ID -- I want the results to list
all instances in table1 where id1 or id2 is a given ID number).
Did I mess up the multiple JOINs? It seems like the SELECT is giving
the correct results other than not ordering by DATE.
Attachments:
signature.asc (application/pgp-signature, 249B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adam@rosi-kessel.org
Subject: Re: ORDER BY does not work as expected with multiple joins
In-Reply-To: <43C7BA12.90103@rosi-kessel.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox