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