From: Martijn Tonies
Date: April 28 2010 2:04pm
Subject: Re: order by numeric value
List-Archive: http://lists.mysql.com/mysql/221446
Message-Id: <027001cae6db$bdd5eb60$6101a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	format=flowed;
	charset="UTF-8";
	reply-type=original
Content-Transfer-Encoding: 7bit


>> > But I'd prefer not to see the extra sorting field.
>>
>> You don't need to select a field in order to be able to order by it.
>>
>> So
>>
>> 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
>> coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0)
>> asc;
>>
>> should do the trick.
>>
>> Jesper
>>
> Perfectamundo!  I thought there would have been a more elegant way but
> this works just fine.  Thanks.

There is a more elegant way: do not do your $ at the database, but rather
in the presentation layer.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com