1

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

1 Answer 1

2

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

Sign up to request clarification or add additional context in comments.

3 Comments

Thank you, then I have to put this in a foreach to take all 400 words, right?
oh, i misread your question, you want all the random words at once. But yes that will work.
I didn't get your funky logic! can you give me a pseudo code? or simple example

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.