InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 15:21:28 GMT20http://www.sqlservercentral.com/Forums/Topic1379722-391-1.aspx[quote][b]28.kanikasoni (11/1/2012)[/b][hr]I 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 solutionnd[/quote]Simplify your model. Pick any one of the queries - the top one is a good choice because it provides the column names. Try this:[code="sql"]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 ciinner join GeoData.TB_Province pr on pr.ProvinceInternalID = ci.CityProvinceIDinner join GeoData.TB_Country co on co.CountryInternalID = ci.CityCountryIDinner join Config.TB_ContractCountryMapping ccm on ccm.ContractCountryMappingCountryInternalID = co.CountryInternalIDwhere 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) +'%'Order By case when (@SortFieldIndex=1 and @OrderBy = 'DESC') then InternalID end DESC, -- no sort case when (@SortFieldIndex=1 and @OrderBy = 'ASC') then InternalID end, -- no sort case when (@SortFieldIndex=2 and @OrderBy = 'DESC') then CityDefaultName end DESC , case when (@SortFieldIndex=2 and @OrderBy = 'ASC') then CityDefaultName 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 end [/code]Do you know how TOP 3 will be evaluated for each subquery?Thu, 01 Nov 2012 07:52:29 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1379722-391-1.aspxI have to agree, we really need to have the DDL (CREATE TABLE statements )for the tables, some sample data (INSERT INTO statements) for each of the tables, expected results.Looking at the code, I am pretty sure that what you want isn't what you have coded. First of all, it really doesn't matter if you sort InternalID ascending or descending as it is always 1 based on your code. If the 2 in the ORDER BY means to sort on the second column, again, you may not be getting what you expect.Thu, 01 Nov 2012 05:35:25 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1379722-391-1.aspxwith this query can you provide some data?Thu, 01 Nov 2012 05:00:37 GMTBriPanhttp://www.sqlservercentral.com/Forums/Topic1379722-391-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 ciinner join GeoData.TB_Province pr on pr.ProvinceInternalID = ci.CityProvinceIDinner join GeoData.TB_Country co on co.CountryInternalID = ci.CityCountryIDinner join Config.TB_ContractCountryMapping ccm on ccm.ContractCountryMappingCountryInternalID = co.CountryInternalIDwhere ci.CityStatusID = 1 and ccm.ContractCountryMappingStatusID = 1 and co.CountryStatusID = 1and ccm.ContractCountryMappingContractInternalID = ltrim(rtrim(str(@ContractID))) and ci.CityZipCodes like SUBSTRING(@FilterString,1,3) +'%'unionselect 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 ciinner join GeoData.TB_Province pr on pr.ProvinceInternalID = ci.CityProvinceIDinner join GeoData.TB_Country co on co.CountryInternalID = ci.CityCountryIDinner join Config.TB_ContractCountryMapping ccm on ccm.ContractCountryMappingCountryInternalID = co.CountryInternalIDwhere ci.CityStatusID = 1 and ccm.ContractCountryMappingStatusID = 1 and co.CountryStatusID = 1and ccm.ContractCountryMappingContractInternalID = ltrim(rtrim(str(@ContractID))) and SOUNDEX(@FilterString) = SOUNDEX(CityDefaultName) unionselect top 3 1 InternalID, ProvinceDefaultName = (pr.ProvinceDefaultName + ',' + co.CountryDefaultName), NULL ParentInternalID, GeoLocation = 'prvnm,ctrnm'from GeoData.TB_Province prinner join GeoData.TB_Country co on co.CountryInternalID = pr.ProvinceCountryIDinner join Config.TB_ContractCountryMapping ccm on ccm.ContractCountryMappingCountryInternalID = co.CountryInternalIDwhere pr.ProvinceStatusID = 1 and ccm.ContractCountryMappingStatusID = 1 and co.CountryStatusID = 1and ccm.ContractCountryMappingContractInternalID = ltrim(rtrim(str(@ContractID))) and SOUNDEX(@FilterString) = SOUNDEX(ProvinceDefaultName)unionselect top 3 1 InternalID, CountryDefaultName, NULL ParentInternalID, GeoLocation = 'ctrnm'from GeoData.TB_Country coinner join Config.TB_ContractCountryMapping ccm on ccm.ContractCountryMappingCountryInternalID = co. CountryInternalIDwhere co.CountryStatusID = 1 and ccm.ContractCountryMappingStatusID = 1and ccm.ContractCountryMappingContractInternalID = ltrim(rtrim(str(@ContractID)))and SOUNDEX(@FilterString) = SOUNDEX(CountryDefaultName) unionselect top 3 1 InternalID, RegionDefaultName, NULL ParentInternalID, GeoLocation = 'reg'from GeoData.TB_Region reginner join GeoData.TB_Country co on reg.RegionInternalID = co.CountryRegionID inner join Config.TB_ContractCountryMapping ccm on ccm.ContractCountryMappingCountryInternalID = co.CountryInternalIDwhere co.CountryStatusID = 1 and ccm.ContractCountryMappingStatusID = 1and reg.RegionStatusID = 1and 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:39:02 GMT28.kanikasoni