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 with order by is not working Expand / Collapse
Author
Message
Posted Thursday, November 1, 2012 4:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 3:15 AM
Points: 15, Visits: 67
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 #1379722
Posted Thursday, November 1, 2012 5:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
with this query can you provide some data?
Post #1379734
Posted Thursday, November 1, 2012 5:35 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 8:19 PM
Points: 23,286, Visits: 32,012
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.



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 #1379750
Posted Thursday, November 1, 2012 7:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 7,219, Visits: 13,685
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1379820
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse