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

[Michael Stassen]

HALIL DEMIREZEN wrote:
> 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);

I think this is not so much a solution as a temporary work-around.  It will stop 
working correctly, without giving any errors, as soon as your table gets to a 
certain size.  Perhaps that will never happen in this particular case, but I 
think this sort of thing is a bad idea, in general.

Your desire is to sort the rows of tablea.  You want all the rows with item = 1 
first, then all the rest.  Within each group (item = 1, item != 1), you want the 
rows in random order.  You should see that this is simply a two step ordering. 
Instead of trying to fake that with unioned queries, you should solve it 
directly in your ORDER BY clause.  Both the query Gleb sent,

   SELECT * FROM tablea ORDER BY IF(item=1,0,1), RAND();

and the one I sent,

  SELECT * FROM tablea ORDER BY (item != 1), RAND();

do just that.  Both will provide the results you asked for, and neither will 
break when the table reaches some particular size.

Michael