SQL – Select column with certain value in it and other random values

Let me say i have a table called test with the following data

+---------+-----------------+
| id      | t_number        |
+---------+-----------------+
|       1 | 864291100247345 |
|       2 | 355488020906457 |
|       3 | 864296100098739 |
|       4 | 864296100098325 |
|       5 | 864296100119956 |

What i want to do is to be able to write a select statement that returns a 3 rows with two random values and one mandatory value from the t_number column for example if the mandatory value is 864291100247345 the output should something like below

+---------+-----------------+
| id      | t_number        |
+---------+-----------------+
|       1 | 864291100247345 |
|       2 | 355488020906457 |
|       4 | 864296100098325 |

+---------+-----------------+
| id      | t_number        |
+---------+-----------------+
|       1 | 864291100247345 |
|       3 | 864296100098739 |
|       4 | 864296100098325 |

I have tried the below query but it’s not yielding the output i expect, in a sense that it does return a result but without the mandatory value

SELECT * FROM test WHERE t_number = 864291100247345 OR id LIMIT 3;

What is the best way to go about this?

Thank you.

Answer

You can use order by:

SELECT t.*
FROM test 
ORDER BY (t_number = 864291100247345) DESC,
         rand()
LIMIT 3;

This returns the mandatory number first and then random numbers after that.

MySQL treats boolean values (the result of the = expression) as numbers in a numeric context, with “1” for true and “0” for false. So the first expression in the order by sorts the result set with the “true” conditions first, followed by the others.

Advertisement

Answer