Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

union and order by Expand / Collapse
Author
Message
Posted Thursday, November 1, 2012 4:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:11 AM
Points: 16, Visits: 70
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
Post #1379721
Posted Thursday, November 1, 2012 5:31 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:11 PM
Points: 20,861, Visits: 32,887
Duplicate post, please post any responses here.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1379748
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse