
- 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
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)
- Related Articles
- MySQL Order by a specific column x and display remaining values in ascending order
- Select last 20 records ordered in ascending order in MySQL?
- Is it possible to divide records in both ascending and descending order in MySQL and display them alternatively?
- MySQL command to order timestamp values in ascending order?
- Order by a single field and display rest of the records in the same order with MySQL
- How to ORDER BY DESC and display the first 3 records in MySQL?
- MySQL command to order timestamp values in ascending order without using TIMESTAMP()?
- Order records and delete n rows in MySQL
- Order randomly in MySQL with a random value column?
- MySQL query to order records but fix a specific name and display rest of the values (only some) random
- Display the count of duplicate records from a column in MySQL and order the result
- How to arrange the fractions in ascending order and descending order?
- Order VARCHAR records with string and numbers in MySQL
- Implement ORDER BY in MySQL to order records in human readable format?
- How can I order in group but randomly with MySQL?

Advertisements