Select a random distinct row

Question

I have a table in mysql called drugs with 3 columns (id,illness,drug).
An illness may occur several times in the column but with different drug.

I need a code to select a distinct ilness but it should use a random criteria so that it can display different drug.

Example in mydb...

id | illness | drug
------------------------------
1  |malaria  |panadol    
2  |malaria  |hedex  
3  |malaria  |tripple action    
4  |fever    |panadol

i tried this but it wasn't random in picking the rows for drug. selected only a single drug for malaria all the time.

$quotes="SELECT drug,remedy FROM drugs group by drug";

i need this outputs

 1   | malaria   | panadol or hedex or tripple action **//any but random**  
 2   | fever     | panadol **//random drug if i add other rows of fever**

`drug` criteria is random...do u have to catch random `illness` too?? or are u giving an `illness` in `where` clause? — NoobEditor, Apr 21 '14 at 07:33
Here http://stackoverflow.com/questions/16044828/select-random-row-for-each-group your question is answered for postgre, Try so that may be useful in mysql too — Saeed, Apr 21 '14 at 07:39
check my question, i have made some edits to show the desired output — Samuel Dervis, Apr 21 '14 at 09:29

Ilesh Patel · Accepted Answer · 2014-04-21T09:40:03.960

1

Try this

$quotes="SELECT drug,remedy FROM drugs WHERE id = (1+ RAND()*(SELECT MAX(id) FROM drugs)) group by drug";

edited Apr 21 '14 at 09:40

answered Apr 21 '14 at 07:29

Ilesh Patel

2,053
16
27

MySQL returned an empty result set (i.e. zero rows). yet i have the various rows in my database. – Samuel Dervis Apr 21 '14 at 09:09
Put your ans here after running it 3-4 times. SELECT FLOOR(RAND()*(SELECT MAX(id) FROM drugs)) – Ilesh Patel Apr 21 '14 at 09:14
`SELECT FLOOR(RAND()*(SELECT MAX(id) FROM drugs));` gives the output 0,3,4,2,7,3,1 – Samuel Dervis Apr 21 '14 at 09:23
what max id you have in your table? – Ilesh Patel Apr 21 '14 at 09:25

score 1 · Answer 2 · answered Apr 21 '14 at 08:08

1

Or in mysql $select = 'select * from drugs order by rand() limit 1'; That will get the rows at random, and then limit answer just for one of it. You can add where clause to the query as well.

answered Apr 21 '14 at 08:08

Seti

2,169
16
26

Select a random distinct row

i need this outputs

2 Answers2