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

On Jan 13, 2006, at 23:32 , Adam Rosi-Kessel wrote:

> 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.

<snip />

> 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

I don't seem to have a problem with the ordering given your data. I'm
not sure why you're doing right and left joins, though. You might
want to use left joins if you have NULL id1 or id2, but otherwise
just plain JOIN should work fine. I've included both your SELECT
(slightly modified to avoid using key words) and how I'd probably
right the statement.

test=# select * from table2;
id | some_name
----+-----------
1 | John
2 | Jane
3 | Sam
(3 rows)

test=# select * from table1;
id1 | id2 | some_date
-----+-----+------------
2 | 1 | 2006-01-05
1 | 2 | 2006-01-01
1 | 3 | 2006-01-03
(3 rows)

test=# SELECT some_date, x.some_name as name1, y.some_name AS name2
FROM table1
LEFT JOIN table2 AS x ON id1 = x.id
RIGHT JOIN table2 AS y ON id2 = y.id
WHERE (some_date IS NOT NULL AND (id1 = 1 OR id2 = 1))
ORDER BY some_date;
some_date | name1 | name2
------------+-------+-------
2006-01-01 | John | Jane
2006-01-03 | John | Sam
2006-01-05 | Jane | John
(3 rows)

test=# select some_date, x.some_name as name1, y.some_name as name2
from table1
join table2 as x on id1 = x.id
join table2 as y on id2 = y.id
where (some_date is not null and (id1 = 1 or id2 = 1))
order by some_date;
some_date | name1 | name2
------------+-------+-------
2006-01-01 | John | Jane
2006-01-03 | John | Sam
2006-01-05 | Jane | John
(3 rows)

Hope this helps.

Michael Glaesemann
grzm myrealbox com

-- DDL
create table table1 (
id1 integer not null
, id2 integer not null
, some_date date not null
);

copy table1 (some_date, id1, id2) from stdin;
2006-01-05 2 1
2006-01-01 1 2
2006-01-03 1 3
\.

create table table2 (
id integer not null
, some_name text not null
);

copy table2 (id, some_name) from stdin;
1 John
2 Jane
3 Sam
\.