[MySQL] Get a random selection of an ordered data set

brianmcgee · 01-11-2011, 07:17 AM

Given the following data structure:

Quote:

data_table
ID | Data | Pos
01 | AAAA | 1
02 | ABAB | 4
03 | AABB | 1
04 | BABA | 4
05 | BBBB | 2
06 | AAAB | 2
07 | ABBB | 3
08 | .... | n

What is the minimum possible MySQL query to get a random data selection that includes each Index number only once and ordered ascending. The maximum index number is known.

For example running the query should get the following data: 01, 06, 07, 02.

Running the query again, you may get the same data or another possible combination like: 01, 05, 07, 04.

Of course you could run a loop for 1 to max_index and process a random ordered data selection until the next index number is found. But I think this will result in too many database access.

Also you could run max_index queries to get all IDs that correspond to the given index and randomly select one ID for each index.

But what will be the most elegant way?

darkduck · 01-11-2011, 07:30 AM

Will LIMIT statement do what you need?
http://dev.mysql.com/doc/refman/5.5/en/select.html

brianmcgee · 01-11-2011, 07:49 AM

Limit would be of use:

Code:

pseudo code:

for i in range(max_index):
    id[i] = mysql_query("SELECT ID FROM data_table WHERE max_index = i ORDER BY RAND() LIMIT 1;")
selection = ",".join(id[])

But I would run max_index mysql queries with maybe lots of returned data for each index just to get one possible set.

Maybe this may be done with a stored procedure?

Still looking for a hint for a smooth solution.

max_index may be a large number and there may be lots of duplicates for each index.

brianmcgee · 01-12-2011, 03:41 AM

Yesterday I found the following solution:

Code:

SELECT r.Pos, (SELECT tbl_test.Data FROM tbl_test WHERE r.Pos=tbl_test.Pos ORDER BY rand() LIMIT 1) AS 'output' FROM tbl_test r GROUP BY r.Pos;