Michael, Thank you and all for effort to help.. I solved the problem by giving high limit numbers such as;
(select * from tablea where item=1 order by rand() limit 0, 100000000) union all (select * from tablea where item != 1 order by rand() limit 0, 100000000); ------------------------------------------------------------ Halil Demirezen System Support Engineer/ Sistem Destek Muhendisi Mobile Tel/Cep Tel: +90(543) 502 04 42 E-Mail/E-Posta: [EMAIL PROTECTED] ----------------------------------------------------------- Michael Stassen <[EMAIL PROTECTED]> 05.12.2005 15:46 To HALIL DEMIREZEN <[EMAIL PROTECTED]> cc Gleb Paharenko <[EMAIL PROTECTED]>, mysql@lists.mysql.com Subject Re: About union sql Mysql 4.x HALIL DEMIREZEN wrote: > Hi, > > I am trying to run an sql query such as below to list items=x randomly and > then items != x randomly.. > > > mysql> (select * from tablea where item=1 order by rand()) union all > (select * from tablea where item != 1 order by rand()); > > but the result is not as expected. rand() seems not to be working... > > What can be the problem? or what can be the difference between my will and > the exact result of the query? Gleb Paharenko wrote: > Hello. > >>From http://dev.mysql.com/doc/refman/5.0/en/union.html: > > "ORDER BY for individual SELECT statements within parentheses has an > effect only when combined with LIMIT. Otherwise, the ORDER BY is > optimized away." > > Therefore you're getting the same results, because ORDER BY doen't > work for your query. HALIL DEMIREZEN wrote: > What if i want to list all the records not limiting them to a constant? How about SELECT * FROM tablea ORDER BY (item != 1), RAND(); Michael
