But I'd prefer not to see the extra sorting field.
Keith
On Tue, 2010-04-27 at 15:49 -0700, Gavin Towey wrote:
> That won't work on a value like "$1.00"
>
> select CAST('$1.00' as decimal(8,2));
> +-------------------------------+
> | CAST('$1.00' as decimal(8,2)) |
> +-------------------------------+
> | 0.00 |
> +-------------------------------+
> 1 row in set, 1 warning (0.00 sec)
>
>
> +---------+------+--------------------------------------------+
> | Level | Code | Message |
> +---------+------+--------------------------------------------+
> | Warning | 1292 | Truncated incorrect DECIMAL value: '$1.00' |
> +---------+------+--------------------------------------------+
> 1 row in set (0.00 sec)
>
>
> It would have to be something like:
>
> select CAST(REPLACE('$1.00','$','') as decimal(8,2)) ;
> +-----------------------------------------------+
> | CAST(REPLACE('$1.00','$','') as decimal(8,2)) |
> +-----------------------------------------------+
> | 1.00 |
> +-----------------------------------------------+
> 1 row in set (0.00 sec)
>
>
> Which in that case, it's better to just select balance without the dollar
> sign and order on that column.
>
> Regards,
> Gavin Towey
>
> -----Original Message-----
> From: DaWiz [mailto:da...@dawiz.net]
> Sent: Tuesday, April 27, 2010 3:46 PM
> To: Keith Clark; mysql@lists.mysql.com
> Subject: Re: order by numeric value
>
> Try
>
> order by CAST(Balance as decimal(8,2)) asc;
>
>
> Cast will work in the order by.
>
> Glenn Vaughn
>
> ----- Original Message -----
> From: "Keith Clark" <keithcl...@k-wbookworm.com>
> To: <mysql@lists.mysql.com>
> Sent: Tuesday, April 27, 2010 3:52 PM
> Subject: order by numeric value
>
>
> >I have the following statement:
> >
> > select chart_of_accounts.accountname as Account,
> > concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as
> > Debit,
> > concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as
> > Credit,
> > concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2))
> > as Balance
> > from sales_journal_entries
> > left join sales_journal
> > on sales_journal.journalID=sales_journal_entries.journalID
> > left join chart_of_accounts
> > on chart_of_accounts.accountID=sales_journal_entries.accountID
> > where sales_journal.date > '2008-12-31'
> > and sales_journal.date < '2010-01-01'
> > group by sales_journal_entries.accountID
> > order by Balance asc;
> >
> > and I'd like the output to be sorted by the Balance according to the
> > numberic value, but it is sorting by the string result. I tried
> > abs(Balance) but I get the following error:
> >
> > 1247 Reference 'Balance' not supported (reference to group function)
> >
> > I'm not sure I understand the error.
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=my...@dawiz.net
>
> This message contains confidential information and is intended only for the
> individual named. If you are not the named addressee, you are notified that
> reviewing, disseminating, disclosing, copying or distributing this e-mail is
> strictly prohibited. Please notify the sender immediately by e-mail if you
> have received this e-mail by mistake and delete this e-mail from your system.
> E-mail transmission cannot be guaranteed to be secure or error-free as
> information could be intercepted, corrupted, lost, destroyed, arrive late or
> incomplete, or contain viruses. The sender therefore does not accept
> liability for any loss or damage caused by viruses or errors or omissions in
> the contents of this message, which arise as a result of e-mail transmission.
> [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA,
> FriendFinder.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
