SQLTeam.com | Weblogs | Forums

Different order by in one statement


#1

Hi,

I would like to use order by for each oddstype, like: In db I have oddstype=1, oddstype=2.

If oddstyype=2 i would like to use order by suggestionid
if oddstype=1 i would like to use order by specailbetvalue.

My code:

SELECT SportId,OddsType,ListName,Sequence
FROM [dbo].[tbl_Suggestions] TS
INNER JOIN dbo.tbl_OddsTypes OT ON TS.OddsType=OT.OddsTypeId
WHERE MatchId=@MatchId AND OddsType IS NOT NULL AND SportId IS NOT NULL AND SportId=@SportId2 --and OddsType='44'
Group By SportId,OddsType,ListName,Sequence
ORDER BY Sequence

	SELECT  COUNT(TS.SuggestionId) OVER (PARTITION BY TS.MatchId,TS.OddsType) AS SuggestionCount,OutCome,OddsType,OutComeId,cast(round(ISNULL(TS.Value,0),2) as numeric(36,2)) AS VALUE ,SuggestionId,TM.MatchDate,TM.MatchId,TM.TournamentId,
    TS.SpecialBetValue
    FROM [dbo].[tbl_Suggestions] TS
    INNER JOIN dbo.tbl_Matches TM ON TS.MatchId=TM.MatchId
    WHERE TS.MatchId=@MatchId AND OddsType IS NOT NULL AND OutCome!='-1' AND SportId=@SportId2  AND TS.Value IS NOT NULL --and OddsType='44'
	ORDER BY TS.SuggestionId

#2
SELECT  COUNT(TS.SuggestionId) OVER (PARTITION BY TS.MatchId,TS.OddsType) AS SuggestionCount,OutCome,OddsType,OutComeId,cast(round(ISNULL(TS.Value,0),2) as numeric(36,2)) AS VALUE ,SuggestionId,TM.MatchDate,TM.MatchId,TM.TournamentId,
    TS.SpecialBetValue
    FROM [dbo].[tbl_Suggestions] TS
    INNER JOIN dbo.tbl_Matches TM ON TS.MatchId=TM.MatchId
    WHERE TS.MatchId=@MatchId AND OddsType IS NOT NULL AND OutCome!='-1' AND SportId=@SportId2  AND TS.Value IS NOT NULL --and OddsType='44'
	ORDER BY OddsType, case when OddsType=2 then TS.SuggestionId else specailbetvalue end