Обсуждение: Wrong "ORDER BY" on a numeric value result
Hello guys, I have following sorting problem and need your help. When executing this SELECT statement: "SELECT d.id, d.name, d.description, ts.name, d.opentimestamp, d.initialvalue, d.plmoney, d.performance, d.performancepa, d.currentopenmoney, d.investedmoney, d.investedpercent, d.cashmoney, d.realizedwinmoney, d.realizedlossmoney, d.currenttotalvalue, d.depotriskpercent, d.taxesratepercent, d.taxallowance, d.paidtaxes, d.paidfees FROM c_depots d INNER JOIN c_tradingsystems ts ON d.tradingsystem_id=ts.id INNERJOIN cx_users_depots cx ON cx.id_depots=d.id INNER JOIN c_users u ON cx.id_users=u.id WHERE u.login='xxxx' ORDER BYUPPER(CAST (d.currenttotalvalue AS numeric) ) DESC" the resulting ordering is wrong: (d.currenttotalvalue) 99999999999,99 9999999999,99 999999999,99 99999,99 100947,51 100251,14 100100 10000000000 10000000000 100000 I would expect: 99999999999,99 9999999999,99 10000000000 10000000000 999999999,99 99999,99 100947,51 100251,14 100100 100000 The column currenttotalvalue has a numeric type with a length of 14 and 2 digits for percision. The initial SELECT didn'tused the CAST, but the result was also wrong. I'm (still) using the postgres version 8.2. Thanks for your support.
2010/8/14 Stefan Wild <wilds81@yahoo.de>
Hello guys,
I have following sorting problem and need your help. When executing this SELECT statement:
"SELECT d.id, d.name, d.description, ts.name, d.opentimestamp, d.initialvalue, d.plmoney, d.performance, d.performancepa, d.currentopenmoney, d.investedmoney, d.investedpercent, d.cashmoney, d.realizedwinmoney, d.realizedlossmoney, d.currenttotalvalue, d.depotriskpercent, d.taxesratepercent, d.taxallowance, d.paidtaxes, d.paidfees FROM c_depots d INNER JOIN c_tradingsystems ts ON d.tradingsystem_id=ts.id INNER JOIN cx_users_depots cx ON cx.id_depots=d.id INNER JOIN c_users u ON cx.id_users=u.id WHERE u.login='xxxx' ORDER BY UPPER(CAST (d.currenttotalvalue AS numeric) ) DESC"
the resulting ordering is wrong:
(d.currenttotalvalue)
99999999999,99
9999999999,99
999999999,99
99999,99
100947,51
100251,14
100100
10000000000
10000000000
100000
I would expect:
99999999999,99
9999999999,99
10000000000
10000000000
999999999,99
99999,99
100947,51
100251,14
100100
100000
The column currenttotalvalue has a numeric type with a length of 14 and 2 digits for percision. The initial SELECT didn't used the CAST, but the result was also wrong.
I'm (still) using the postgres version 8.2.
Thanks for your support.
I'm just curious why do you use UPPER() function in the ORDER BY clause?
regards
Szymon
On 14 August 2010 22:46, Stefan Wild <wilds81@yahoo.de> wrote: > Hello guys, > > I have following sorting problem and need your help. When executing this SELECT statement: > > "SELECT d.id, d.name, d.description, ts.name, d.opentimestamp, d.initialvalue, d.plmoney, d.performance, d.performancepa, d.currentopenmoney, d.investedmoney, d.investedpercent, d.cashmoney, d.realizedwinmoney, d.realizedlossmoney, d.currenttotalvalue, d.depotriskpercent, d.taxesratepercent, d.taxallowance, d.paidtaxes, d.paidfees FROM c_depots d INNER JOIN c_tradingsystems ts ON d.tradingsystem_id=ts.idINNER JOIN cx_users_depots cx ON cx.id_depots=d.id INNER JOIN c_users u ON cx.id_users=u.id WHEREu.login='xxxx' ORDER BY UPPER(CAST (d.currenttotalvalue AS numeric) ) DESC" > > the resulting ordering is wrong: > (d.currenttotalvalue) > 99999999999,99 > 9999999999,99 > 999999999,99 > 99999,99 > 100947,51 > 100251,14 > 100100 > 10000000000 > 10000000000 > 100000 > > I would expect: > > 99999999999,99 > 9999999999,99 > 10000000000 > 10000000000 > 999999999,99 > 99999,99 > 100947,51 > 100251,14 > 100100 > 100000 > > The column currenttotalvalue has a numeric type with a length of 14 and 2 digits for percision. The initial SELECT didn'tused the CAST, but the result was also wrong. > > I'm (still) using the postgres version 8.2. > > Thanks for your support. > I'm confused as to why UPPER appears in your ORDER BY clause. -- Thom Brown Registered Linux user: #516935
On Sat, Aug 14, 2010 at 09:46:37PM +0000, Stefan Wild wrote: > Hello guys, > > I have following sorting problem and need your help. When executing this SELECT statement: > > "SELECT d.id, d.name, > d.description, ts.name, d.opentimestamp, > d.initialvalue, d.plmoney, d.performance, > d.performancepa, d.currentopenmoney, d.investedmoney, > d.investedpercent, d.cashmoney, d.realizedwinmoney, > d.realizedlossmoney, d.currenttotalvalue, d.depotriskpercent, > d.taxesratepercent, d.taxallowance, d.paidtaxes, > d.paidfees FROM c_depots d INNER JOIN c_tradingsystems ts > ON d.tradingsystem_id=ts.id INNER JOIN cx_users_depots cx ON > cx.id_depots=d.id INNER JOIN c_users u ON cx.id_users=u.id WHERE > u.login='xxxx' ORDER BY UPPER(CAST (d.currenttotalvalue AS numeric) ) > DESC" > The column currenttotalvalue has a numeric type with a length of 14 and 2 digits for percision. The initial SELECT didn'tused the CAST, but the result was also wrong. column is numeric, but upper() works on text, and returns text, so your numeric column got casted to text by using upper (which is pointless anyway - there is no "upper" version of digits). remove upper() and you'll be fine. depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
Thank you guys! That was the point. --- hubert depesz lubaczewski <depesz@depesz.com> schrieb am So, 15.8.2010: > Von: hubert depesz lubaczewski <depesz@depesz.com> > Betreff: Re: [GENERAL] Wrong "ORDER BY" on a numeric value result > An: "Stefan Wild" <wilds81@yahoo.de> > CC: pgsql-general@postgresql.org > Datum: Sonntag, 15. August, 2010 05:53 Uhr > On Sat, Aug 14, 2010 at 09:46:37PM > +0000, Stefan Wild wrote: > > Hello guys, > > > > I have following sorting problem and need your help. > When executing this SELECT statement: > > > > "SELECT > d.id, > d.name, > > d.description, > ts.name, > d.opentimestamp, > > d.initialvalue, > d.plmoney, > d.performance, > > d.performancepa, > d.currentopenmoney, > d.investedmoney, > > d.investedpercent, > d.cashmoney, > d.realizedwinmoney, > > > d.realizedlossmoney, d.currenttotalvalue, d.depotriskpercent, > > d.taxesratepercent, d.taxallowance, > d.paidtaxes, > > d.paidfees > FROM c_depots d INNER JOIN > c_tradingsystems ts > > ON d.tradingsystem_id=ts.id INNER JOIN cx_users_depots > cx ON > > cx.id_depots=d.id INNER JOIN c_users u ON > cx.id_users=u.id WHERE > > u.login='xxxx' ORDER BY UPPER(CAST > (d.currenttotalvalue AS numeric) ) > > DESC" > > The column currenttotalvalue has a numeric type with a > length of 14 and 2 digits for percision. The initial SELECT > didn't used the CAST, but the result was also wrong. > > column is numeric, but upper() works on text, and returns > text, so your > numeric column got casted to text by using upper (which is > pointless > anyway - there is no "upper" version of digits). > remove upper() and you'll be fine. > > depesz > > -- > Linkedin: http://www.linkedin.com/in/depesz > / blog: http://www.depesz.com/ > jid/gtalk: depesz@depesz.com > / aim:depeszhdl / skype:depesz_hdl / gg:6749007 >
Stefan Wild wrote on 15.08.2010 10:36: >> column is numeric, but upper() works on text, and returns >> text, so your >> numeric column got casted to text by using upper (which is >> pointless >> anyway - there is no "upper" version of digits). >> remove upper() and you'll be fine. >> > Thank you guys! That was the point. The real question is: what did you try to accomplish with the UPPER() on a numeric column? Regards Thomas
The answer is quite simple: this statement was composed by several functions used for ordering on string values as also onnumeric. --- Thomas Kellerer <spam_eater@gmx.net> schrieb am So, 15.8.2010: > Von: Thomas Kellerer <spam_eater@gmx.net> > Betreff: Re: [GENERAL] Wrong "ORDER BY" on a numeric value result > An: pgsql-general@postgresql.org > Datum: Sonntag, 15. August, 2010 10:43 Uhr > Stefan Wild wrote on 15.08.2010 > 10:36: > >> column is numeric, but upper() works on text, and > returns > >> text, so your > >> numeric column got casted to text by using upper > (which is > >> pointless > >> anyway - there is no "upper" version of digits). > >> remove upper() and you'll be fine. > >> > > > Thank you guys! That was the point. > > The real question is: > > what did you try to accomplish with the UPPER() on a > numeric column? > > Regards > Thomas > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >