union all with order by newid()???

Question

Hai friends,

here im getting error when im using union all and order by newid() together, can any one help me to solve this issue..

select distinct * from(select top 500 (A.AdId),A.AdImgPath,A.OnAdURL,A.AdSize from KZ_AdDetails A,KZ_Add_Cat Cat,KZ_Add_City Cit where A.AddU_ID=Cat.AddU_ID and A.AddU_ID=Cit.AddU_ID and AdSize='250x250' order by newid()) X

union all

select distinct * from(select top 500 (A.AdId),A.AdImgPath,A.OnAdURL,A.AdSize from KZ_AdDetails A,KZ_Add_Cat Cat,KZ_Add_District Dis where A.AddU_ID=Cat.AddU_ID and A.AddU_ID=Dis.AddU_ID and AdSize='250x250' order by newid()) Y

regards
Divya

Iftikar Hussain · Answer

Hi, Modify your query as like below select distinct * from( select top 500 (A.AdId),A.AdImgPath,A.OnAdURL,A.AdSize,newid() as Id from KZ_AdDetails A,KZ_Add_Cat Cat,KZ_Add_City Cit where A.AddU_ID=Cat.AddU_ID and A.AddU_ID=Cit.AddU_ID and AdSize='250x250' union all select top 500 (A.AdId),A.AdImgPath,A.OnAdURL,A.AdSize,newid() as Id from KZ_AdDetails A,KZ_Add_Cat Cat,KZ_Add_District Dis where A.AddU_ID=Cat.AddU_ID and A.AddU_ID=Dis.AddU_ID and AdSize='250x250' ) AS X order by Id Regards, Iftikar Remember to click "Mark as Answer" on the post, if it helps you.

Pankaj Pandey · Answer

yes mam, i have varified with insertion of some data manually. it working....may be this problem is comming with live data.. id you can then do one thing...export data into csv format of all tables, and upload here, i will check it then may be i help you..

divya · Answer

Thanku Pankaj Pandey sir,Have u verified that order by newid is working properly??????

Jignesh Trivedi · Answer

hi, with the test data it work fine for me. Are you getting any error? or data mismatch?

Pankaj Pandey · Answer

HII i have check it but in my machine its working well with data. may be this problem is with your data.

Pankaj Pandey · Answer

HII i have check it but in my machine its working well with data.

divya · Answer

Hai Thanku for u r response.Here im uploading tables ,please kindly find the attached file.Order by newid() is not working with union all... Regards Divya

Pankaj Pandey · Answer

hello please share table scripts and error also.

Jignesh Trivedi · Answer

hi Divya, Query look ok, and also it work with newid() for my test data. Can you please error desc?

union all with order by newid()???

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (9)