Just joined the list and have seen in the archive the thread on orderby. But mine seems different.
I have a finanancial application with account and transactions which go between accounts (source and destination) but in which one either can be null. I am attempting to read an account and sort all the transactions by date. Here are the transactions id | date | src | dst | description | amount ----+------------+-------+-------+---------------------------+-------- 11 | 2005-06-05 | | Sarah | Sarah Petrol | 27.74 12 | 0005-06-05 | Sarah | | Ben 21st Birthday Pressie | -41 13 | 0005-06-05 | | Sarah | Cash from Mum | 60 14 | 0005-06-08 | | Sarah | Petrol | 27.33 15 | 0005-06-10 | | Sarah | Petrol Allowance | -40 This is the SQL select name, id, transaction.date as tdate, description, -amount as amount from account left join transaction on name=src where name = 'Sarah' union select name, id, transaction.date as tdate, description, amount from account join transaction on name=dst where name ='Sarah' order by tdate asc; name | id | tdate | description | amount -------+----+------------+---------------------------+-------- Sarah | 12 | 0005-06-05 | Ben 21st Birthday Pressie | 41 Sarah | 13 | 0005-06-05 | Cash from Mum | 60 Sarah | 14 | 0005-06-08 | Petrol | 27.33 Sarah | 15 | 0005-06-10 | Petrol Allowance | -40 Sarah | 11 | 2005-06-05 | Sarah Petrol | 27.74 (5 rows) I can't figure out why the dates are not in order (see transaction 11 is out of place). for reference the transaction table has the "date" field of type "date" -- Alan Chandler http://www.chandlerfamily.org.uk Open Source. It's the difference between trust and antitrust. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
