Fix a specific column value and display random values for rest of the rows in MySQL

For random rows, you can use RAND(), whereas to fix a specific column, use ORDER BY clause. Let us create a table −

mysql> create table DemoTable1921
   (
   Number int
   );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1921 values(40);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1921 values(80);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1921 values(820);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1921 values(10);
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1921;

This will produce the following output −

+--------+
| Number |
+--------+
|     40 |
|     80 |
|    820 |
|     10 |
+--------+
4 rows in set (0.00 sec)

Here is the query to fix a specific column value and display rest of the rows with random values −

mysql> select * from DemoTable1921
   order by (Number=40)desc,rand();

This will produce the following output −

+--------+
| Number |
+--------+
|     40 |
|    820 |
|     10 |
|     80 |
+--------+
4 rows in set (0.00 sec)

AmitDiwan

Published on 30-Dec-2019 11:42:19