Sort order criteria? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sort order criteria?

Author

Topic

plawrenz
Starting Member

15 Posts

Posted - 2007-11-02 : 10:00:36

I am using a curser to populate a temp table but I need the sort order to show all bonds where CountryCde=@CountryCde and CurrencyCde=@CurrencyCde first before they only meet the CurrencyCde =@CurrencyCde criteria. The Any Ideas, use two temp queries and then how do I combine them?

declare c_getbonds cursor
for select BondNum,
PortfolioCde,
AvailableParAmt,
BookValueAmt,
MarketValueAmt,
UnrlzGainPerParAmt,
ThrtyDayRuleInd,
UseBondNxtFutCde,
CountryCde,
CurrencyCde
from WorkBondsInventory
where PortfolioCde = @PortfolioCde
and
((CountryCde = @CountryCde and CurrencyCde=@CurrencyCde)
or
(CurrencyCde = @CurrencyCde))
and
AvailableParAmt > 0
order by UseBondNxtFutCde asc,ThrtyDayRuleInd asc, UnrlzGainPerParAmt desc

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 10:13:55

try a new column like this...
CASE WHEN CountryCde=@CountryCde and CurrencyCde=@CurrencyCde THEN 0 WHEN CurrencyCde =@CurrencyCde THEN 1 ELSE 2 END AS SortCriteria
and an order by like this...
ORDER BY SortCriteria

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-02 : 10:15:08

No need of even adding new column, you can put the CASE Expression in ORDER BY clause itself.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 10:35:47

good point harsh. the query performance is the same, but because you are using a cursor you should exclude the column unless you expect to use the column for other logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources