Re: About union sql Mysql 4.x

[HALIL DEMIREZEN]

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: [phone]
E-Mail/E-Posta: halil.demirezen@stripped
-----------------------------------------------------------




Michael Stassen <Michael.Stassen@stripped> 
05.12.2005 15:46

To
HALIL DEMIREZEN <halil.demirezen@stripped>
cc
Gleb Paharenko <gleb.paharenko@stripped>, mysql@stripped
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