28.kanikasoni (11/1/2012)
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
Simplify your model. Pick any one of the queries - the top one is a good choice because it provides the column names. Try this:
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) +'%'
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
Do you know how TOP 3 will be evaluated for each subquery?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden