lundi 16 mars 2015

SQL - How to randomly choose an ordered subset?

I have a table which roughly looks like so:



id | unique | group
----------------------------------
1 | aaa | group1
2 | bbb | group1
3 | ggg | group1
4 | iii | group2
5 | ooo | group2
6 | eee | group3


I woul like to choose a somewhat random subset of size N from this table. I want my result be a list of uniques but "ordered" by the group. For example, in the above case, with N == 3, I would expect to have [ooo, iii, eee] or [iii, ooo, eee] or [eee, iii, ooo or [eee, ooo, iii] returned (or any combination of group1 uniques). I do not do this very often and I prefer multiple queries over one complex. N will be around 100 or so, the table has <10k entries and the programm runs once perday. N does not need to be exacly matched. I am doing this in Python/Mysqldb but I'm rather interested in the general algorithm/query.


Additionally, once the N entries are processed, I would like to ignore them on the next run, either by simply removing them or by setting some additonal attribute. In the latter case, the queries would need to be adapted.


Aucun commentaire:

Enregistrer un commentaire