Random and duplicates with order by?

Suppose I have a table like so,

unqiue_data int(10),
not_unique_data int (10)

unique_data not_unique_data
1            1
2            1
3            2
4            2
5            2
select * from some_table order by not_unique_data DESC;

What I need to do, is randomize this SELECT query, but in a very two particular ways that I just can’t figure out how to do. Firstly, I want unique_data randomized, so that the SELECT query could return something like (randomly):

unique_data not_unique_data
2              1
1              1 
4              2
3              2
5              2

The second requirement I have is that, unique_data appears multiple times, but in a very specific order.

In an ideal world, I need is so that it could return something like

unique_data not_unique_data

4              2
3              2
5              2
1              1
2              1
3              2
5              2
4              2
2              1
1              1 
5              2
4              2
3              2

What I mean by this is, I need it so that each unique_data (4,3,5), (3,5,4), (5,4,3) The first number of each set appears only once while still being ordered by not_unique_data.

How to do this?

Answer

Well for this problem you have to make sure that 100 products related to a product

• how many of them have appeared for that product
• how many of them will be appeared for that product

We can use a temporary table to do so

SELECT unique_data, not_unique_data, 0
INTO temp_newtable
FROM some_table
ORDER BY RAND()

Now we will get a randomly organized table and by default seen=0 (seen to know it has been appeared for that product or not)

unique_data not_unique_data seen

4              2             1
3              2             1
5              2             0
1              1             0
2              1             0
3              2             1

So whenever some product related to product appear on page you need to update seen column to 1, when you are out of this table truncate and generate random data for usage again