[SQL] 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.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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Another orderby problem
am 14.01.2006, um 13:02:48 + 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=== ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Another orderby problem
On Sat, 14 Jan 2006 13:02:48 + 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 | 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
Re: [SQL] Another orderby problem
On Saturday 14 January 2006 13:09, A. Kretschmer wrote: > am 14.01.2006, um 13:02:48 + 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] psql client: technique for applying default values to :variables?
Tony Wasson wrote:
On 1/11/06, Jeff Boes <[EMAIL PROTECTED]> wrote:
Stumped: is there any way to set up default values for psql variables
within the .SQL file itself? Obviously, I can do something like:
$ psql -f my_script -v MYVAR=${myvar:-mydefault}
but I would prefer to have the value stored with the .SQL file, e.g. (if
this actually worked):
\set MYVAR COALESCE(:MYVAR,'mydefault')
Stuff like this works for me in a SQL file
\set edate 'CURRENT_DATE::DATE'
SELECT * FROM some_table WHERE update_date = :edate;
Sure, but that was not my question. I want to be able to set the
variable on the command line, BUT have it default to a value inside the
SQL script if not present on the command line.
$ psql -v edate=2004-01-01
...
=> \set edate 'CURRENT_DATE::DATE'
=> select :edate;
date
2006-01-14
--
Jeffery Boes <><
[EMAIL PROTECTED]
Re: [SQL] psql client: technique for applying default values to :variables?
On Jan 14, 2006, at 23:54 , Jeff Boes wrote: Tony Wasson wrote: Stuff like this works for me in a SQL file \set edate 'CURRENT_DATE::DATE' SELECT * FROM some_table WHERE update_date = :edate; Sure, but that was not my question. I want to be able to set the variable on the command line, BUT have it default to a value inside the SQL script if not present on the command line. You could write a wrapper script that would accept a command line argument and load the file with the appropriate SET statement prepended. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] select and as doubt
Hello folks!!! I've a question that could be very strange: It's possible to make an SELECT inside an AS clause ? ex.: select student_id, subject_name_id, grade as (select subject_name from subject_names) from grades; The wy is that i've a table with subject names and another with name_id and grades like: Table subject_names ( id, subject_name); Table students ( id, name); Table grades ( student_id, subject_name_id, grade) My goal is display an report that have this layout: Name| Math | Bio | English Joe Doe | 10 | 8 | 6 Mary| 8| 6 | 5 I try to make this with crosstab, but I need that columns with suject grades are displayed automaticlly Thanks. -- __ Alguns caminham pelo arco, eu caminho pela reta. Alexandre Gonçalves Jacarandá Assessor de Tecnologia de Informação Tel.: 0 ** 21 8131-2313 ___ Yahoo! doce lar. Faça do Yahoo! sua homepage. http://br.yahoo.com/homepageset.html ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] select and as doubt
=?ISO-8859-1?Q?Alexandre_Gon=E7alves_Jacarand=E1?= <[EMAIL PROTECTED]> writes: > It's possible to make an SELECT inside an AS clause ? No. You could try building the query as a string. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
