List:General Discussion« Previous MessageNext Message »
From:DaWiz Date:April 27 2010 10:46pm
Subject:Re: order by numeric value
View as plain text  
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 

Attachment: [application/x-pkcs7-signature] smime.p7s
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