mysql query to select one specific row and another random row.

Question

Hi all.

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

Answer 1

Try this

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

Answer 2

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 

Answer 3

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)

Answer 4

Try this:

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