MySQL sort

MySQL Sorting

We know that we use the SQL SELECT statement to read data from a MySQL table.

If we need to sort the read data, we can use MySQL's ORDER BY clause to set which field you want to sort by, and then return the search results.

Syntax

The following is a SQL SELECT statement that uses the ORDER BY clause to sort the query data before returning the data:

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

• You can use any field as a sorting condition to return sorted query results.

• You can set multiple fields to sort.

• You can use the ASC or DESC keyword to set the query results to be sorted in ascending or descending order. By default, it is sorted in ascending order.

• You can add WHERE...LIKE clause to set conditions.
  

Using the ORDER BY clause in the command prompt

The following will be used in the SQL SELECT statement Use the ORDER BY clause to read the data in the MySQL data table user:

Example

Try the following example, the results will be sorted in ascending and descending order.

mysql> use demo;

Database changed

mysql> SELECT * from user ORDER BY submission_date ASC;

+------ ----+---------------+---------------+------------- ----+

|user_id | user_title | user_author | submission_date |

+-----------+----------- ----+---------------+------------------+

| 3 | Learn Python | PHP Chinese Network | 2016-03-06 |

| 1 | Learn PHP | PHP Chinese Network | 2017-04-12 | -04-12 |

+-----------+---------------+---------- -----+-----------------+

3 rows in set (0.01 sec)

mysql> SELECT * from user ORDER BY submission_date DESC;

+-----------+---------------+- ---------------+-----------------+

| user_id | user_title | user_author | submission_date |

+-----------+---------------+---------------+- ----------------+

| 1 | Learn PHP | PHP Chinese website | 2017-04-12 |

| 2 | Learn MySQL | PHP Chinese Network | 2017-04-12 |

|3 | Learning Python | PHP Chinese Network | 2016-03-06 |

+---------- -+---------------+------------------+---------------- -+

3 rows in set (0.01 sec)

Read all the data in the user table and sort it in ascending order of the submission_date field.

Using the ORDER BY clause in PHP scripts

You can use the PHP function mysqli_query() with the same SQL SELECT with the ORDER BY clause command to get data.

This function is used to execute SQL commands, and then output all queried data through the PHP function mysqli_fetch_array().

Example

Try the following example. The queried data is returned in descending order of the submission_date field.