Re: order by numeric value

[DaWiz]

Try

order by CAST(Balance as decimal(8,2)) asc;


Cast will work in the order by.

Glenn Vaughn


To: <mysql@lists.mysql.com>
Sent: Tuesday, April 27, 2010 3:52 PM
Subject: order by numeric value

----- Original Message ----- From: "Keith Clark" <keithcl...@k-wbookworm.com>

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,

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

concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2)) as Balance To unsubscribe: http://lists.mysql.com/mysql?unsub=my...@dawiz.net

smime.p7s
Description: S/MIME cryptographic signature