CODEWITHSUNDEEP
mysqlrandom
codingbbq
codingbbq

Reputation: 4179

mysql query to select one specific row and another random row

I want to display two records.

For eg select * FROM users WHERE user_id = 5.

Now i want another row randomly selected from users table but with user_id != 5

Is it possible to do in a single query. I tried using union all but i dnt get two distinct rows.

Thanks

Upvotes: 6

Views: 4015

Answers (4)

Pekka
Pekka

Reputation: 449415

This works fine for me. The first result is always the record with ID 5, the second row is a random one. Note that if no record with the ID 5 exists, both rows will be random.

SELECT * FROM users ORDER BY (user_id = 5) DESC, RAND() LIMIT 0,2

Upvotes: 7

Dewfy
Dewfy

Reputation: 23624

Try this:

SELECT * FROM users WHERE user_id=5
union all (
  SELECT * FROM  users WHERE user_id!=5
  ORDER BY RAND() LIMIT 0,1)

Upvotes: 0

jwueller
jwueller

Reputation: 30996

Union should do the trick. You probably forgot LIMIT.

(SELECT * FROM users WHERE user_id = 5 LIMIT 1)
UNION
(SELECT * FROM users WHERE user_id != 5 LIMIT 1)

Upvotes: 0

Mads Lee Jensen
Mads Lee Jensen

Reputation: 4648

Try this 

SELECT * FROM users WHERE user_id = 5 || user_id != 5 ORDER BY RAND() LIMIT 2

Upvotes: 0

Related Questions