Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SORTING with ORDER by for a FLOAT field Expand / Collapse
Author
Message
Posted Wednesday, June 11, 2008 12:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, June 11, 2008 1:29 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:03 PM
Points: 6,266, Visits: 2,027
I don't quite understand how you determined that "order". Can you be more specific ?



* Noel
Post #515467
Posted Friday, June 13, 2008 8:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 03, 2013 1:08 PM
Points: 11, Visits: 69
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
Posted Friday, June 13, 2008 8:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 »

Add to briefcase

Permissions Expand / Collapse