Another orderby problem

From: Alan Chandler <alan(at)chandlerfamily(dot)org(dot)uk>
To: pgsql-sql(at)postgresql(dot)org
Subject: Another orderby problem
Date: 2006-01-14 13:02:48
Message-ID: 200601141302.49630.alan@chandlerfamily.org.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message A. Kretschmer 2006-01-14 13:09:02 Re: Another orderby problem
Previous Message Jaime Casanova 2006-01-13 16:20:11 Re: ORDER BY does not work as expected with multiple joins