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

brianmcgee 01-11-2011 08:17 AM

[MySQL] Get a random selection of an ordered data set
Given the following data structure:

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 08:30 AM

Will LIMIT statement do what you need?

brianmcgee 01-11-2011 08:49 AM

Limit would be of use:

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 04:41 AM

Yesterday I found the following solution:


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;

All times are GMT -5. The time now is 01:10 PM.