Re: About union sql Mysql 4.x

[Michael Stassen <suppressed>]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/suppressed