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(,0),2)) as
>> Credit,
> concat('$',format(coalesce(sum(,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 > '2008-12-31'
>> and < '2010-01-01'
>> group by sales_journal_entries.accountID
>> order by
> coalesce(sum(,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

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

Database questions? Check the forum: 

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