On Sat, 14 Jan 2006 13:02:48 +0000 Alan Chandler <[EMAIL PROTECTED]> wrote: > 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).
It looks to be in order to me. Transaction 11 is 2,000 years later than the others. I suppose you are letting people enter two digit dates without running it through a cleaner process. If you put "05" as a year into a date field, PostgreSQL will take you at your word. if year < 50: year += 2000 elif year < 100: year += 1900 That will probably handle most cases for you. Alternatively, don't allow dates outside of your known domain. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
