How to get random rows in inner join using mysql?

Question

I am using following queries to fetch random rows,

'SELECT * FROM TABLENAME1 ORDER BY RAND() LIMIT 1' . this query will return random rows.But i need to get the name which is placed in another table.so here i have to use join queries.

'SELECT tablename1.*,tablename2.name FROM tablename1 INNER JOIN tablename2 ON tablename1.id = tablename2.id ORDER BY RAND(tablename1.id)'

The above queries return same rows everytime, I am not getting random rows.Kindly help me on this.

hughes · Accepted Answer · 2011-08-03 13:07:24Z

1

RAND(tablename1.id) uses tablename1.id as the seed for the pseudorandom number generator, so it will give you the same result every time. Try just using RAND() in your second query.

answered Aug 3, 2011 at 13:07

hughes

5,7113 gold badges41 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Widor · Accepted Answer · 2011-08-03 13:07:56Z

1

For some reason, you're seeding the 2nd version of the query with tablename1.id. Calls to RAND() with the same seed value return the same results.

answered Aug 3, 2011 at 13:07

Widor

13.3k7 gold badges47 silver badges67 bronze badges

Collectives™ on Stack Overflow

How to get random rows in inner join using mysql?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related