CODEWITHSUNDEEP
mysql
Adam
Adam

Reputation: 20952

MYSQL + Select 2 columns - 1 being Unique

I have a table like the following:

 domainname    english
 www.a.com     apples
 www.a.com     peaches
 www.b.com     oranges
 www.b.com     banana
 www.c.com     watermelon

I want to select a random list of domainname and english but where the domainname is unique. for example:

 www.a.com     apples
 www.b.com     banana
 www.c.com     watermelon

The results need to be random each time.

I've read other examples on this site and tried the following but it doesn't give a unique list of domainnames - eg: they show up twice or more:

 SELECT DISTINCT(domainname),english FROM table WHERE domainname ORDER BY RAND();

Hopeing someone can give some advice.

cheers

Upvotes: 1

Views: 138

Answers (3)

Devart
Devart

Reputation: 122032

Try this query:

SELECT * FROM (
  SELECT * FROM table ORDER BY RAND()) t
GROUP BY
  domainname

Upvotes: 3

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

If the english column is the PRIMARY KEY of the table (or has a UNIQUE constraint), you could also use this:

SELECT t.*
FROM 
      ( SELECT DISTINCT domainname
        FROM tableX
      ) AS dd
  JOIN
      tableX AS t
    ON t.english =
       ( SELECT tt.english  
         FROM tableX AS tt
         WHERE tt.domainname = dd.domainname
         ORDER BY RAND()
         LIMIT 1
       )

Upvotes: 0

Mulesoft Developer
Mulesoft Developer

Reputation: 2824

Try this query distinct is not working with multiple column name

SELECT DISTINCT(domainname) FROM table WHERE domainname ORDER BY RAND();

Upvotes: 0

Related Questions