CODEWITHSUNDEEP
mysqlsql
Rab Ko
Rab Ko

Reputation: 163

how to select 2 random category data

My table struture is as follows (with data):

+------------------------------------+
|id    fname     category  city      |
|1     ram       cook      kathmandu |
|2     shyam     cook      lalitpur  |
|3     krishna   maid      patan     |
|4     jay       maid      kupondole |
|5     rajan     maid      ratnapark |
+------------------------------------+

The query should return 2 rows (cook and maid) at once with random cook and maid. How can I do this?

Upvotes: 1

Views: 78

Answers (3)

chetan
chetan

Reputation: 2886

The sub query is responsible for finding the random id

SELECT *
FROM mytable
WHERE id IN
    (SELECT round(rand()*(max(id)-min(id)+1))+min(id)
     FROM mytable
     GROUP BY category);

Upvotes: 1

Raging Bull
Raging Bull

Reputation: 18747

Two alternatives:

(SELECT * FROM TableName WHERE category='cook' ORDER BY rand() limit 1)
UNION ALL
(SELECT * FROM TableName WHERE category='maid' ORDER BY rand() limit 1)

See result in SQL Fiddle.

OR

SELECT * FROM TableName 
WHERE category IN ('cook','maid') 
GROUP BY category 
ORDER BY rand() limit 2

See result in SQL Fiddle.

Result:

ID  FNAME   CATEGORY    CITY
1   ram     cook        kathmandu
5   rajan   maid        ratnapark

Upvotes: 2

arunmoezhi
arunmoezhi

Reputation: 3200

select fname
from table
where category="cook"
order by RAND()
LIMIT 1
UNION
select fname
from table
where category="maid"
order by RAND()
LIMIT 1

Upvotes: 1

Related Questions