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/[EMAIL PROTECTED]