CODEWITHSUNDEEP
phpmysqlrandom
John Hudson
John Hudson

Reputation: 83

PHP MySQL select random rows where a row is different

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

Upvotes: 2

Views: 64

Answers (1)

cb0
cb0

Reputation: 8613

The main problem with my solution may be that it's

  1. slow

  2. 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.

Upvotes: 2

Related Questions