InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 00:04:20 GMT20http://www.sqlservercentral.com/Forums/Topic1379721-338-1.aspxDuplicate post, please post any responses [url=http://www.sqlservercentral.com/Forums/Topic1379722-391-1.aspx][b]here[/b][/url].Thu, 01 Nov 2012 05:31:12 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1379721-338-1.aspxI had used 5 queries and union their result then appllied order by clause .but order by doesn't work result comes same every timeplz provide solutionWITH CTE as ( select top 3 1 InternalID, CityDefaultName = (ci.CityZipCodes + ',' + ci.CityDefaultName + ',' + pr.ProvinceDefaultName + ',' + co.CountryDefaultName), NULL ParentInternalID, GeoLocation = (cast(ci.CityLatitude as varchar(20))+ ',' + cast(ci.CityLongitude as varchar(20))) from GeoData.TB_City ci inner join GeoData.TB_Province pr on pr.ProvinceInternalID = ci.CityProvinceID inner join GeoData.TB_Country co on co.CountryInternalID = ci.CityCountryID inner join Config.TB_ContractCountryMapping ccm on ccm.ContractCountryMappingCountryInternalID = co.CountryInternalID where ci.CityStatusID = 1 and ccm.ContractCountryMappingStatusID = 1 and co.CountryStatusID = 1 and ccm.ContractCountryMappingContractInternalID = ltrim(rtrim(str(@ContractID))) and ci.CityZipCodes like SUBSTRING(@FilterString,1,3) +'%' union select top 3 1 InternalID, CityDefaultName = (ci.CityDefaultName + ',' + pr.ProvinceDefaultName + ',' + co.CountryDefaultName), NULL ParentInternalID, GeoLocation = (cast(ci.CityLatitude as varchar(20))+ ',' + cast(ci.CityLongitude as varchar(20))) from GeoData.TB_City ci inner join GeoData.TB_Province pr on pr.ProvinceInternalID = ci.CityProvinceID inner join GeoData.TB_Country co on co.CountryInternalID = ci.CityCountryID inner join Config.TB_ContractCountryMapping ccm on ccm.ContractCountryMappingCountryInternalID = co.CountryInternalID where ci.CityStatusID = 1 and ccm.ContractCountryMappingStatusID = 1 and co.CountryStatusID = 1 and ccm.ContractCountryMappingContractInternalID = ltrim(rtrim(str(@ContractID))) and SOUNDEX(@FilterString) = SOUNDEX(CityDefaultName) union select top 3 1 InternalID, ProvinceDefaultName = (pr.ProvinceDefaultName + ',' + co.CountryDefaultName), NULL ParentInternalID, GeoLocation = 'prvnm,ctrnm' from GeoData.TB_Province pr inner join GeoData.TB_Country co on co.CountryInternalID = pr.ProvinceCountryID inner join Config.TB_ContractCountryMapping ccm on ccm.ContractCountryMappingCountryInternalID = co.CountryInternalID where pr.ProvinceStatusID = 1 and ccm.ContractCountryMappingStatusID = 1 and co.CountryStatusID = 1 and ccm.ContractCountryMappingContractInternalID = ltrim(rtrim(str(@ContractID))) and SOUNDEX(@FilterString) = SOUNDEX(ProvinceDefaultName) union select top 3 1 InternalID, CountryDefaultName, NULL ParentInternalID, GeoLocation = 'ctrnm' from GeoData.TB_Country co inner join Config.TB_ContractCountryMapping ccm on ccm.ContractCountryMappingCountryInternalID = co. CountryInternalID where co.CountryStatusID = 1 and ccm.ContractCountryMappingStatusID = 1 and ccm.ContractCountryMappingContractInternalID = ltrim(rtrim(str(@ContractID))) and SOUNDEX(@FilterString) = SOUNDEX(CountryDefaultName) union select top 3 1 InternalID, RegionDefaultName, NULL ParentInternalID, GeoLocation = 'reg' from GeoData.TB_Region reg inner join GeoData.TB_Country co on reg.RegionInternalID = co.CountryRegionID inner join Config.TB_ContractCountryMapping ccm on ccm.ContractCountryMappingCountryInternalID = co.CountryInternalID where co.CountryStatusID = 1 and ccm.ContractCountryMappingStatusID = 1 and reg.RegionStatusID = 1 and ccm.ContractCountryMappingContractInternalID = ltrim(rtrim(str(@ContractID))) and SOUNDEX(@FilterString) = SOUNDEX(RegionDefaultName) ) select * from cte Order By case when (@SortFieldIndex=1 and @OrderBy = 'DESC') then InternalID end DESC,case when (@SortFieldIndex=1 and @OrderBy = 'ASC') then InternalID end, case when (@SortFieldIndex=2 and @OrderBy = 'DESC') then 2 end DESC , case when (@SortFieldIndex=2 and @OrderBy = 'ASC') then 2 end,case when (@SortFieldIndex=4 and @OrderBy = 'DESC') then GeoLocation end DESC,case when (@SortFieldIndex=4 and @OrderBy = 'ASC') then GeoLocation end,case when (@SortFieldIndex not IN(1,2,4)) then 2 endThu, 01 Nov 2012 04:37:27 GMT28.kanikasoni