CODEWITHSUNDEEP
mysqlsqlrandom
Filippo oretti
Filippo oretti

Reputation: 49873

Get random record in a set of results

I have a simple MySQL query like this:

 SELECT * ,
 ( MATCH (table.get) AGAINST('playstation ' IN BOOLEAN MODE) ) 
+ ( table.get LIKE '%playstation%') AS _score 
FROM table 
JOIN users on table.id_user = users.id
 WHERE table.expire_datetime > 1375997618
 HAVING _score > 0 
ORDER BY RAND(table.id) ,_score DESC ;

If I run this query in MySQL, it returns usually more then 1 record, now I would like to LIMIT 1 and get one of them randomly, not always the same record.

Is it possible?

Upvotes: 0

Views: 74

Answers (3)

trijin
trijin

Reputation: 483

As I understand problem in ,_score ? Try this:

Select * FROM (
   ***your sql query***
) as t
ORDER BY RAND()
LIMIT 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271051

You would quit seeding the random number generator. My guess is that it is returning the first table id encountered, so the numbers are generated in the same sequence:

 SELECT * ,
 ( MATCH (table.get) AGAINST('playstation ' IN BOOLEAN MODE) ) 
+ ( table.get LIKE '%playstation%') AS _score 
FROM table 
JOIN users on table.id_user = users.id
 WHERE table.expire_datetime > 1375997618
 HAVING _score > 0 
ORDER BY RAND()
LIMIT 1;

Upvotes: 1

Vijay Kukkala
Vijay Kukkala

Reputation: 362

select * from <my_table> 
ORDER BY RAND()
LIMIT 4

Upvotes: 2

Related Questions