November 1, 2012 at 4:37 am
I had used 5 queries and union their result then appllied order by clause .but order by doesn't work result comes same every time
plz provide solution
WITH 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 end
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply