What Im Trying to achieve is selecting multiple rows randomly where a certain column is different from the last for example
SELECT * FROM mytable WHERE `column_for_duplicate_values` != '$thelastkey' ORDER BY RAND() LIMIT 10
the column for multiple values would hold for example:
1 , 1 , 1 , 1 , 2, 5 , 6, 6 , 6 , 6 , 6 , 11 , 11 , 11 , 19
and I want to select 1 from each. So I would get
1 , 2 , 5 , 6 , 11 , 19
The main problem with my solution may be that it's
not applicable
You need to have a primary unique id in your mytable
SELECT * FROM mytable WHERE id IN (
SELECT id FROM (
SELECT id, column_for_duplicate_values
FROM mytable ORDER BY RAND()) AS rand
GROUP BY column_for_duplicate_values
ORDER BY RAND()) LIMIT 10;
The main concept is to first get a random list of all values with their id and the column you want to have unique values.
From this result we group by the value we only want to have once and only return the id. The main table will then select 10 random id's form this list.
I tried it with one of my db's. Should work with any table that has a unique id and some random other column.
If you append ORDER BY id ASC will also sort the id's.
