
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
MySQL query to select one specific row and another random row?n
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)
- Related Articles
- 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)?
- How to find specific row with a MySQL query?
- MySQL query to get a specific row from rows
- Select random row that exists in a MySQL table?
- MySQL query to generate row index (rank) in SELECT statement?
- MySQL query to delete row
- MySQL query to select rows except first row in descending order?
- Write a Python code to select any one random row from a given DataFrame
- MySQL insert a value to specific row and column
- How to select last row in MySQL?
- MySQL LIMIT to select a single row
- MySQL query to insert row with date?
- Comparing two columns in a single MySQL query to get one row?

Advertisements