Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


union with order by is not working


union with order by is not working

Author
Message
28.kanikasoni
28.kanikasoni
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 80
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
BriPan
BriPan
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 296
with this query can you provide some data?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24179 Visits: 37948
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.

Cool
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)
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8959 Visits: 19017
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search