pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAnother orderby problem
4+ messages / 3 participants
[nested] [flat]
* Another orderby problem
@ 2006-01-14 13:02 Alan Chandler <alan@chandlerfamily.org.uk>
0 siblings, 2 replies; 4+ messages in thread
From: Alan Chandler @ 2006-01-14 13:02 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Another orderby problem
@ 2006-01-14 13:09 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Alan Chandler <alan@chandlerfamily.org.uk>
1 sibling, 1 reply; 4+ messages in thread
From: A. Kretschmer @ 2006-01-14 13:09 UTC (permalink / raw)
To: pgsql-sql
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 ===
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Another orderby problem
@ 2006-01-14 13:13 D'Arcy J.M. Cain <darcy@druid.net>
parent: Alan Chandler <alan@chandlerfamily.org.uk>
1 sibling, 0 replies; 4+ messages in thread
From: D'Arcy J.M. Cain @ 2006-01-14 13:13 UTC (permalink / raw)
To: Alan Chandler <alan@chandlerfamily.org.uk>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Another orderby problem
@ 2006-01-14 14:28 Alan Chandler <alan@chandlerfamily.org.uk>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 4+ messages in thread
From: Alan Chandler @ 2006-01-14 14:28 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-01-14 14:28 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-14 13:02 Another orderby problem Alan Chandler <alan@chandlerfamily.org.uk>
2006-01-14 13:09 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-01-14 14:28 ` Alan Chandler <alan@chandlerfamily.org.uk>
2006-01-14 13:13 ` D'Arcy J.M. Cain <darcy@druid.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox