union all with order by newid()???

divya

13y2.8k

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

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
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 Pandey13y ago
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..
divya13y ago

Thanku Pankaj Pandey sir,Have u verified that order by newid is working properly??????
Jignesh Trivedi13y ago
hi,
with the test data it work fine for me.
Are you getting any error? or data mismatch?
Pankaj Pandey13y ago
HII

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

may be this problem is with your data.
Pankaj Pandey13y ago
HII

i have check it but in my machine its working well with data.
divya13y ago
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 Pandey13y ago
hello

please share table scripts and error also.
Jignesh Trivedi13y ago
hi Divya,

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