Re: About union sql Mysql 4.x In-reply-to: <[e-mail]>

[Michael Stassen]

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