Selecting random balanced records based on two columns

Question

I want to select 6 random records from this table based off 2 columns, the topic and the taxonomy.The selection of records must be balanced and repetition of topics and taxonomy should be kept a minimum.Is there an algorithm to do this?

Any help in either SQL or java would be appreciated.Thanks in advance

Possible duplicate of [How to request a random row in SQL?](http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql) — Jon Saw, Feb 26 '17 at 16:43
Please see [Why is “Can someone help me?” not an actual question?](http://meta.stackoverflow.com/q/284236) — EJoshuaS - Stand with Ukraine, Feb 26 '17 at 20:14

score 0 · Accepted Answer · answered Feb 26 '17 at 05:01

You can do this with an nth sample on an ordered set. It is something like:

select t.*
from (select t.*, (@rn := @rn + 1) as rn
      from t cross join
           (select @rn := 0) params
      order by topics, taxonomy
     ) t cross join
     (select count(*) as cnt from t) tt
where rn % floor(cnt / 6) = 1;

The idea is to use modulo arithmetic to take every nth value to get to 6. You may have to fiddle with the exact parameters in the where, depending on the size of your data.

Thank you for the help, I think i can get it to work the way i expect it to with some tweaks to the where part. — Ajith, Feb 26 '17 at 05:24

Selecting random balanced records based on two columns

1 Answers1