6

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

Improve this question

4 Answers 4

Reset to default
7

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

THanks pekka. I tried your query. It works great. +1. What if i have a condition. Can i use it in there. say eg SELECT * FROM users ORDER BY (now() BETWEEN start_date and end_date) DESC, RAND() LIMIT 0,2 ??
@noobcode sure, depending on what you want to do. The random row won't be covered by the condition this way
Simply entering the condition did not work.So i put a subquery. This is how the query looks."" SELECT * FROM users ORDER BY (user_id = (select user_id FROM users WHERE the_condition)) DESC, RAND() LIMIT 0,2 "" This works perfect. Thanks a lot. Appreciate.
0

Try this 

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

1 Comment

Won't work: Will not necessarily give user ID 5 as the first result
0

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)
Improve this answer

Comments

0

Try this:

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.