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.7412 | 0005-06-05 | Sarah | | Ben 21st Birthday Pressie | -4113 | 0005-06-05 | | Sarah |
Cashfrom Mum | 6014 | 0005-06-08 | | Sarah | Petrol | 27.3315 | 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 |
41Sarah| 13 | 0005-06-05 | Cash from Mum | 60Sarah | 14 | 0005-06-08 | Petrol |
27.33Sarah| 15 | 0005-06-10 | Petrol Allowance | -40Sarah | 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.
