From: Alan Chandler <alan@chandlerfamily.org.uk>
To: pgsql-sql@postgresql.org
Subject: Another orderby problem
Date: Sat, 14 Jan 2006 13:02:48 +0000
User-Agent: KMail/1.9.1
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200601141302.49630.alan@chandlerfamily.org.uk>

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.