MySQL query to select one specific row and another random row?

To select one specific row and another random row, you can use ORDER BY and RAND(). Let us first create a sample table:

mysql> create table oneSpecificRowAndOtherRandom
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> Name varchar(20)
   -> );
Query OK, 0 rows affected (0.72 sec)

Following is the query to insert some records in the table using insert command:

mysql> insert into oneSpecificRowAndOtherRandom(Name) values('Larry');
Query OK, 1 row affected (0.56 sec)

mysql> insert into oneSpecificRowAndOtherRandom(Name) values('Sam');
Query OK, 1 row affected (0.13 sec)

mysql> insert into oneSpecificRowAndOtherRandom(Name) values('Mike');
Query OK, 1 row affected (0.12 sec)

mysql> insert into oneSpecificRowAndOtherRandom(Name) values('Carol');
Query OK, 1 row affected (0.15 sec)

mysql> insert into oneSpecificRowAndOtherRandom(Name) values('Chris');
Query OK, 1 row affected (0.21 sec)

mysql> insert into oneSpecificRowAndOtherRandom(Name) values('Bob');
Query OK, 1 row affected (0.13 sec)

mysql> insert into oneSpecificRowAndOtherRandom(Name) values('David');
Query OK, 1 row affected (0.13 sec)

Following is the query to display records from the table using select command:

mysql> select *from oneSpecificRowAndOtherRandom;

This will produce the following output

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Larry |
| 2  | Sam   |
| 3  | Mike  |
| 4  | Carol |
| 5  | Chris |
| 6  | Bob   |
| 7  | David |
+----+-------+
7 rows in set (0.00 sec)

Following is the query to select one specific row and another random row:

mysql> select *from oneSpecificRowAndOtherRandom ORDER BY (Id= 5) DESC, RAND() LIMIT 0,3;

This will produce the following output

+----+-------+
| Id | Name  |
+----+-------+
| 5  | Chris |
| 1  | Larry |
| 2  | Sam   |
+----+-------+
3 rows in set (0.05 sec)

Let us run the same query again to display random records since we have used RAND():

mysql> select *from oneSpecificRowAndOtherRandom ORDER BY (Id= 5) DESC, RAND() LIMIT 0,3;

This will produce the following output

+----+-------+
| Id | Name  |
+----+-------+
| 5  | Chris |
| 2  | Sam   |
| 4  | Carol |
+----+-------+
3 rows in set (0.00 sec)

George John

Published on 17-Apr-2019 11:44:03

• Related Questions & Answers
• Select a random row in MySQL
• MySQL query to select maximum and minimum salary row?
• MySQL query to select a random row value (Id and Name) having multiple occurrences (Name)?
• Select random row that exists in a MySQL table?
• How to find specific row with a MySQL query?
• MySQL query to get a specific row from rows
• MySQL query to delete row
• MySQL query to generate row index (rank) in SELECT statement?
• Write a Python code to select any one random row from a given DataFrame
• MySQL insert a value to specific row and column
• MySQL query to select rows except first row in descending order?
• Display random row from a MySQL table
• How to select last row in MySQL?
• MySQL LIMIT to select a single row
• Comparing two columns in a single MySQL query to get one row?