Wrong "ORDER BY" on a numeric value result

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.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 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'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

Thank you guys! That was the point.

--- hubert depesz lubaczewski <depesz@depesz.com> schrieb am So, 15.8.2010:

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 on numeric.

--- Thomas Kellerer <spam_eater@gmx.net> schrieb am So, 15.8.2010: