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?