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

Published on 16-Jan-2019 10:12:14

Previous Page Print Page