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 07:12:19

• Related Questions & Answers
• MySQL query to order records but fix a specific name and display rest of the values (only some) random
• Fix a row value and then ORDER BY DESC rest of the values in MySQL
• Ignore null values in MySQL and display rest of the values
• MySQL random rows sorted by a specific column name?
• Fetch the size of a specific column values in MySQL and display the sum
• Place a specific value for NULL values in a MySQL column
• Set a specific value for the first three column values in MySQL?
• Compare for NULL value and display value 1 for these values in a new MySQL column?
• Select a specific value between two column values in MySQL?
• MySQL Order by a specific column x and display remaining values in ascending order
• Find rows where column value ends with a specific substring in MySQL?
• Fetch specific rows from a MySQL table with duplicate column values (names)?
• Swap a specific column value in MySQL
• MySQL insert a value to specific row and column
• Implementing incremental search and display the values with a specific number in MySQL?