Ahmad Mobaraki Ahmad Mobaraki - 1 year ago 75
MySQL Question

Laravel select random rows from table based on another field

There is a

words
table with 20000 records:

ID name rank
1 word1 3
2 word2 5019
3 word3 12334
4 word4 23
5 word5 544


I want to select 400 words randomly from this table but with this condition :

first 20 words: select 20 words randomly from
words
with rank between 1 and 1000

second 20 words: select 20 words randomly from
words
with rank between 1000 and 2000

And so on...

Do I have to do this in 20 separate queries? How? Is there a better way?

I am using laravel 5.4 and Mysql, Also a raw query suggestion would be appreciated. Thank you

Answer Source

Easy way

use a where clause to filter their rank, then use inRandomOrder() and take(20) to get 20 random ones.

Word::inRandomOrder()->where('rank', '>=', 1)->where('rank', '<=', 1000)->take(20);

Hard way

To get them all in one query, you might try some funky logic like this:

first: define a view that returns the same table, but instead of rank, has categories, so category 1 for 1<=rank<1000, .... just to make the next step easier

now we can use partition by (see Trying to understand over() and partition by). Remember to order by RAND() inside the partition. Order the result of all this by rownumber.

Now we have a result that looks like this:

rownumber      name      category
1              word1     1 
1              word2     2 
1              word3     3 
1              word4     4 
...
2              word21    1 
2              word22    2 
2              word23    3 
2              word24    4 
...
20             word381   1 
20             word382   2 
20             word383   3 
20             word384   4 
...

By taking 400 of these tuples, we will have 20 random samples of each of the 20 categories.

Note-- ordering by RAND() can be slow, as explained here http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/

Edit: turn out partition by is for sql server only. But you could do something similar in mysql

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download