Order MySQL records randomly and display name in Ascending order

You can use subquery to order randomly and display name in asending order. The rand() is used for random, whereas ORDER BY is used to display name records in ascending order. The syntax is as follows −

select *from
(
   select *from yourTableName order by rand() limit anyIntegerValue;
) anyVariableName
order by yourColumnName;

To understand the above concept, let us create a table. We have an ID as sell as Name, which we want in Ascending order. The query to create a table is as follows −

mysql> create table OrderByRandName
   −> (
   −> Id int,
   −> Name varchar(100)
   −> );
Query OK, 0 rows affected (0.96 sec)

Display all records from the table using insert command. The query is as follows −

mysql> insert into OrderByRandName values(100,'John');
Query OK, 1 row affected (0.18 sec)

mysql> insert into OrderByRandName values(101,'Bob');
Query OK, 1 row affected (0.11 sec)

mysql> insert into OrderByRandName values(102,'Johnson');
Query OK, 1 row affected (0.19 sec)

mysql> insert into OrderByRandName values(103,'David');
Query OK, 1 row affected (0.22 sec)

mysql> insert into OrderByRandName values(104,'Smith');
Query OK, 1 row affected (0.17 sec)

mysql> insert into OrderByRandName values(105,'Taylor');
Query OK, 1 row affected (0.20 sec)

mysql> insert into OrderByRandName values(106,'Sam');
Query OK, 1 row affected (0.12 sec)

mysql> insert into OrderByRandName values(107,'Robert');
Query OK, 1 row affected (0.22 sec)

mysql> insert into OrderByRandName values(108,'Michael');
Query OK, 1 row affected (0.16 sec)

mysql> insert into OrderByRandName values(109,'Mark');
Query OK, 1 row affected (0.17 sec)

Display all records using select statement. The query is as follows −

mysql> select *from OrderByRandName;

The following is the output −

+------+---------+
| Id   | Name    |
+------+---------+
|  100 | John    |
|  101 | Bob     |
|  102 | Johnson |
|  103 | David   |
|  104 | Smith   |
|  105 | Taylor  |
|  106 | Sam     |
|  107 | Robert  |
|  108 | Michael |
|  109 | Mark    |
+------+---------+
10 rows in set (0.00 sec)

Here is the query that order by rand() and display name in ascending order −

mysql> select *from
   −> (
   −> select *from OrderByRandName order by rand() limit 10
   −> )tbl1
   −> order by Name;

The following is the output −

+------+---------+
| Id   | Name    |
+------+---------+
|  101 | Bob     |
|  103 | David   |
|  100 | John    |
|  102 | Johnson |
|  109 | Mark    |
|  108 | Michael |
|  107 | Robert  |
|  106 | Sam     |
|  104 | Smith   |
|  105 | Taylor  |
+------+---------+
10 rows in set (0.39 sec)

Jennifer Nicholas

Updated on 30-Jul-2019 22:30:24

• Related Questions & Answers
• MySQL Order by a specific column x and display remaining values in ascending order
• Select last 20 records ordered in ascending order in MySQL?
• MySQL command to order timestamp values in ascending order?
• Is it possible to divide records in both ascending and descending order in MySQL and display them alternatively?
• Order by a single field and display rest of the records in the same order with MySQL
• MySQL command to order timestamp values in ascending order without using TIMESTAMP()?
• Order records and delete n rows in MySQL
• How to ORDER BY DESC and display the first 3 records in MySQL?
• Order randomly in MySQL with a random value column?
• Order VARCHAR records with string and numbers in MySQL
• MySQL query to order records but fix a specific name and display rest of the values (only some) random
• MySQL order by 0 first and then display the record in descending order?
• How can we sort MySQL output in ascending order?
• MySQL query to first set negative value in descending order and then positive value in ascending order
• Implement ORDER BY in MySQL to order records in human readable format?