CODEWITHSUNDEEP
mysql
David19801
David19801

Reputation: 11448

mysql random with condition

I have a table in mysql, say table1.

I am running this on it:

SELECT FLOOR( MAX(id) * RAND()) FROM `table1`

This works well, but I am now trying to add a condition of "AND tom".

Where tom is a integer field.

For example:

id tom
1   0
2   3
3   2
4   0
5   0
6   3
7   1
8   1
9   3

etc.

So, my question is,

How can I pick a random value from id, which also satisfies tom='0' say?

Upvotes: 0

Views: 398

Answers (3)

Mike Lewis
Mike Lewis

Reputation: 64137

SELECT id FROM `table1` WHERE tom = 0 ORDER BY RAND() LIMIT 1

This will first get all rows in which tom = 0,then order those results randomly. MySQL will then limit those results to just one, returning the single value you want to retrieve.

Upvotes: 2

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

I hope I understood correctly:

SELECT id FROM `table1` WHERE tom = 0 order by rand() limit 1

Upvotes: 1

Nicola Cossu
Nicola Cossu

Reputation: 56357

select * from (
select * from table where tom = 0 ) as t order by rand() limit 1

Upvotes: 0

Related Questions