Thread: Another orderby problem
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.
am 14.01.2006, um 13:02:48 +0000 mailte Alan Chandler folgendes: > 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). '0005-06-10' < '2005-06-05'. Perhaps you have a wrong year. HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net=== Schollglas Unternehmensgruppe ===
On Sat, 14 Jan 2006 13:02:48 +0000 Alan Chandler <alan@chandlerfamily.org.uk> 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.
On Saturday 14 January 2006 13:09, A. Kretschmer wrote: > am 14.01.2006, um 13:02:48 +0000 mailte Alan Chandler folgendes: > > 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). > > '0005-06-10' < '2005-06-05'. Perhaps you have a wrong year. Thanks Its amazing how long you can stare at something and not see the obvious. -- Alan Chandler http://www.chandlerfamily.org.uk Open Source. It's the difference between trust and antitrust.