List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:April 28 2010 2:04pm
Subject:Re: order by numeric value
View as plain text  
>> > 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 

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