union with order by is not working

Question

Post reply

union with order by is not working

28.kanikasoni

Mr or Mrs. 500

Points: 526
More actions
November 1, 2012 at 4:39 am

#264971

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 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

BriPan SSCrazy Points: 2061 More actions · Answer 1

BriPan

SSCrazy

Points: 2061

November 1, 2012 at 5:00 am

#1554940

with this query can you provide some data?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

I 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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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 time
plz 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?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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