InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 04:42:42 GMT20http://www.sqlservercentral.com/Forums/Topic1379220-338-1.aspxYou could use Phil's suggested query as a subquery. This lets you add as many CASE statements with as many WHEN statements as you want and it lets you filter out those unwanted columns in the final query result. The following code should give you an idea of what I mean. Obviously the OrderBy2 part would not have the same syntax as OrderBy1.[code="sql"]SELECT sub.VacancyInternalID ,sub.VacancyTitle ,sub.ParentInternalID ,sub.GeoLocation FROM ( select distinct VacancyInternalID ,VacancyTitle ,null ParentInternalID ,null GeoLocation ,OrderBy1= ( case @SortFieldIndexConfig when 1 then VacancyInternalID when 2 then VacancyTitle else VacancyInternalID end ) ,OrderBy2= ( case @SortFieldIndexConfig when 1 then VacancyInternalID when 2 then VacancyTitle else VacancyInternalID end ) from Vacancy.TB_Vacancy va inner join Config.TB_Contract co on va.VacancyContractID = co.ContractInternalID where co.ContractExternalID = '492A94D0-7D71-46E5-A8F6-E3A973394647' and co.ContractStatusID = 1 and VacancyStatusID = 1 and VacancyTitle like '%%' ) sub ORDER BY sub.OrderBy1 DESC, sub.OrderBy2 DESC[/code]Thu, 01 Nov 2012 07:16:20 GMTken.willekenshttp://www.sqlservercentral.com/Forums/Topic1379220-338-1.aspxBut here I am getting one extra column which I don't want. and also in other cases I need to use multiple case statement with order by because different datatype of columns create problem in single case statement with multiple whens.Plz provide solution to this probThu, 01 Nov 2012 03:16:08 GMT28.kanikasonihttp://www.sqlservercentral.com/Forums/Topic1379220-338-1.aspxThank you for your excellent solution now its working fine:w00t:Thu, 01 Nov 2012 03:06:36 GMT28.kanikasonihttp://www.sqlservercentral.com/Forums/Topic1379220-338-1.aspx[quote][b]dwain.c (10/31/2012)[/b][hr][quote][b]28.kanikasoni (10/31/2012)[/b][hr]Hi,Thanks 4 ur solution but I am getting error in order clauseIncorrect syntax near the keyword 'order'.Incorrect syntax near the keyword 'ELSE'.Incorrect syntax near the keyword 'ELSE'.[/quote]Try removing the parentheses around the CASE statement in the ORDER BY clause.[/quote]Actually, I think the problem might be that I used the reserved word 'Order' as column alias. Try this:[code="sql"]select distinct VacancyInternalID ,VacancyTitle ,null ParentInternalID ,null GeoLocation ,OrderBy = ( case @SortFieldIndexConfig when 2 then VacancyTitle else VacancyInternalID end )from Vacancy.TB_Vacancy vainner join Config.TB_Contract co on va.VacancyContractID = co.ContractInternalIDwhere co.ContractExternalID = '492A94D0-7D71-46E5-A8F6-E3A973394647' and co.ContractStatusID = 1 and VacancyStatusID = 1 and VacancyTitle like '%%'order by ( case @SortFieldIndexConfig when 2 then VacancyTitle else VacancyInternalID end ) desc[/code]Thu, 01 Nov 2012 01:58:56 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1379220-338-1.aspx[quote][b]28.kanikasoni (10/31/2012)[/b][hr]Hi,Thanks 4 ur solution but I am getting error in order clauseIncorrect syntax near the keyword 'order'.Incorrect syntax near the keyword 'ELSE'.Incorrect syntax near the keyword 'ELSE'.[/quote]Try removing the parentheses around the CASE statement in the ORDER BY clause.Wed, 31 Oct 2012 23:52:07 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1379220-338-1.aspxHi,Thanks 4 ur solution but I am getting error in order clauseIncorrect syntax near the keyword 'order'.Incorrect syntax near the keyword 'ELSE'.Incorrect syntax near the keyword 'ELSE'.Wed, 31 Oct 2012 22:15:29 GMT28.kanikasonihttp://www.sqlservercentral.com/Forums/Topic1379220-338-1.aspxJust add the CASE construct to your SELECT. The CASE construct can be made more compact too:[code="sql"]select distinct VacancyInternalID ,VacancyTitle ,null ParentInternalID ,null GeoLocation ,order = ( case @SortFieldIndexConfig when 2 then VacancyTitle else VacancyInternalID end )from Vacancy.TB_Vacancy vainner join Config.TB_Contract co on va.VacancyContractID = co.ContractInternalIDwhere co.ContractExternalID = '492A94D0-7D71-46E5-A8F6-E3A973394647' and co.ContractStatusID = 1 and VacancyStatusID = 1 and VacancyTitle like '%%'order by ( case @SortFieldIndexConfig when 2 then VacancyTitle else VacancyInternalID end ) desc[/code]Wed, 31 Oct 2012 04:25:32 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1379220-338-1.aspxHi i am trying to use select distinct with order by case but getting an error like [color=#990000]"ORDER BY items must appear in the select list if SELECT DISTINCT is specified."[/color]select DISTINCT VacancyInternalID , VacancyTitle ,NULL ParentInternalID , NULL GeoLocation from Vacancy.TB_Vacancy va inner join Config.TB_Contract co on va.VacancyContractID = co.ContractInternalID where co.ContractExternalID = '492A94D0-7D71-46E5-A8F6-E3A973394647' and co.ContractStatusID = 1 and VacancyStatusID = 1 and VacancyTitle like '%%' order by case when @SortFieldIndexConfig=1 then VacancyInternalID end DESC, case when @SortFieldIndexConfig=2 then VacancyTitle end DESC,case when @SortFieldIndexConfig NOT IN(1,2) then VacancyInternalID end DESC I want a solution without subquery for this problem.can any one help on this topicWed, 31 Oct 2012 04:01:37 GMT28.kanikasoni