SORTING with ORDER by for a FLOAT field

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

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

* Noel

paulchernoch SSC-Addicted Points: 441 More actions · Answer 2

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

jcollins SSC-Addicted Points: 481 More actions · Answer 3

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