SORTING with ORDER by for a FLOAT field

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » SORTING with ORDER by for a FLOAT field

SORTING with ORDER by for a FLOAT field

Rate Topic

Display Mode

Topic Options

Author

Message

jcollins

Posted Wednesday, June 11, 2008 12:14 PM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, August 11, 2012 7:36 AM
Points: 31, Visits: 54

select top 5 left(convert(varchar(10),EffectiveDate,101),10) [Date]

, convert(varchar,cast(NetMarketValue as money),1) [NetMarketValue]

, convert(varchar,cast(NetGainLoss as money),1) [NetGainLoss]

FROM Investment.Portfolio.vForwardContractExposure

WHERE (EffectiveDate IN (SELECT MAX(EffectiveDate) FROM Investment.Portfolio.vForwardContractHolding))

AND (Broker = ' ')

AND (CustomGroup = ' ')

AND (SourceAccount = ' ')

AND (SourceAccountId = ' ')

ORDER BY NetGainLoss desc

THE field NetGainLoss is defined as a float field within the database table 'Investment.Portfolio.vForwardContractExposure'

What tricks if any would return the correct results for example with whole numbers 1, 55, 9

it would return results in this order ...

9
55
1

jcollins

Post #515424

noeld

Posted Wednesday, June 11, 2008 1:29 PM

SSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260, Visits: 1,977

I don't quite understand how you determined that "order". Can you be more specific ?

* Noel

Post #515467

paulchernoch

Posted Friday, June 13, 2008 8:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:11 PM
Points: 9, Visits: 49

Your problem is that you converted the number to a string, which sorts alphabetically, not numerically.

I would use a different name for the alias of the sort column so you can return the nicely formatted value,
then sort by the underlying numerical column. (Your alias is hiding the underlying column from the scope of the order by, I think.)

select top  5 left(convert(varchar(10),EffectiveDate,101),10) [Date] 
      , convert(varchar,cast(NetMarketValue as money),1) [NetMarketValue]
      , convert(varchar,cast(NetGainLoss as money),1) [NetGainLossFormatted]
   FROM     Investment.Portfolio.vForwardContractExposure
         WHERE    (EffectiveDate IN (SELECT MAX(EffectiveDate) FROM Investment.Portfolio.vForwardContractHolding)) 
        AND (Broker          = ' ')
        AND (CustomGroup     = ' ')
        AND (SourceAccount   = ' ')
        AND (SourceAccountId = ' ')
ORDER BY NetGainLoss desc

- Paul

Post #516723

jcollins

Posted Friday, June 13, 2008 8:53 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, August 11, 2012 7:36 AM
Points: 31, Visits: 54

Thanks Everyone,

Paul is correct I figured this out shortly after the posting.

After renaming the Alias and sorting by the actual database field
the sort order works fine.

Thanks again,

Jeff

jcollins

Post #516784

« Prev Topic | Next Topic »

Permissions