Re: Wrong "ORDER BY" on a numeric value result

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(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007