List:General Discussion« Previous MessageNext Message »
From:Keith Clark Date:April 27 2010 10:53pm
Subject:RE: order by numeric value
View as plain text  
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:dawiz@stripped]
> Sent: Tuesday, April 27, 2010 3:46 PM
> To: Keith Clark; mysql@stripped
> 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" <keithclark@stripped>
> To: <mysql@stripped>
> 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=1
> 
> 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


Thread
order by numeric valueKeith Clark27 Apr
  • Re: order by numeric valueCarsten Pedersen28 Apr
    • Re: order by numeric valueKeith Clark28 Apr
Re: order by numeric valueDaWiz28 Apr
  • RE: order by numeric valueGavin Towey28 Apr
    • RE: order by numeric valueKeith Clark28 Apr
      • Re: order by numeric valueJesper Wisborg Krogh28 Apr
        • Re: order by numeric valueKeith Clark28 Apr
  • Re: order by numeric valueMartijn Tonies28 Apr