English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية

MySql Tutorial

MySQL Sorting

We know that we can read data from MySQL tables using SQL SELECT statements.

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

Syntax

The following SQL SELECT statement uses the ORDER BY clause to sort the query data and then return the data:

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

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

  • You can sort by multiple fields.

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

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

Using ORDER BY clause in the command prompt

The following will use the ORDER BY clause in the SQL SELECT statement to read MySQL table w3Data in codebox_tbl:

Online Example

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

mysql> use w3codebox;
Database changed
MariaDB [w3codebox> SELECT　*　from w3codebox_tbl ORDER BY submission_date ASC;
+----------+-------------+--------------+-----------------+
| w3codebox_id | w3codebox_title | w3codebox_author | submission_date |
+----------+-------------+--------------+-----------------+
|　　　　　　　　1　| Learn PHP           | oldtoolbag.com           |　2018-04-14　　　　　　|
|　　　　　　　　2　| Learn MySQL         | oldtoolbag.com           |　2018-04-14　　　　　　|
|　　　　　　　　3　| JAVA Tutorial        | oldtoolbag.com           |　2018-04-14　　　　　　|
|　　　　　　　　4　| Learn Python        | oldtoolbag.com           |　2019-06-08　　　　　　|
+----------+-------------+--------------+-----------------+
4　rows in set (0.00 sec)
MariaDB [w3codebox> SELECT　*　from w3codebox_tbl ORDER BY submission_date DESC;
+----------+-------------+--------------+-----------------+
| w3codebox_id | w3codebox_title | w3codebox_author | submission_date |
+----------+-------------+--------------+-----------------+
|　　　　　　　　4　| Learn Python        | oldtoolbag.com           |　2019-06-08　　　　　　|
|　　　　　　　　1　| Learn PHP           | oldtoolbag.com           |　2018-04-14　　　　　　|
|　　　　　　　　2　| Learn MySQL         | oldtoolbag.com           |　2018-04-14　　　　　　|
|　　　　　　　　3　| JAVA Tutorial        | oldtoolbag.com           |　2018-04-14　　　　　　|
+----------+-------------+--------------+-----------------+
4　rows in set (0.02　sec)

Read w3All data in the codebox_tbl table and sorted in ascending order by the submission_date field.

Using ORDER BY clause in PHP script

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

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

Online Example

Try the following example, and the returned data is sorted in descending order by the submission_date field after the query.

<?php
$dbhost = 'localhost';　　//　MySQL server host address
$dbuser = 'root';　　　　　　　　　　　　//　MySQL username
$dbpass = '123456';　　　　　　　　　　//　MySQL username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if (!$conn)
{
　　　　die('Connection failed: ' . mysqli_error($conn));
}
//　Set encoding to prevent Chinese garbled characters
mysqli_query($conn, "set names utf8');
　
$sql = 'SELECT w3codebox_id, w3codebox_title,　
　　　　　　　　w3codebox_author, submission_date
　　　　　　　　FROM w3codebox_tbl
　　　　　　　　ORDER BY submission_date ASC';
　
mysqli_select_db($conn, 'w3codebox');
$retval = mysqli_query($conn, $sql);
if(! $retval)
{
　　　　die('Unable to read data: ' . mysqli_error($conn));
}
echo '<h2">Basic Tutorial Website MySQL ORDER BY Test<h2">
echo '<table border="1><tr><td> Tutorial ID</td><td>Title</td><td>Author</td><td>Submission Date</td></tr>';
while($row = mysqli_fetch_array($retval))
{
　　　　echo "<tr><td> {$row['w3codebox_id']}</td> ".
　　　　　　　　　"<td>{$row['w3codebox_title']} </td> ".
　　　　　　　　　"<td>{$row['w3codebox_author']} </td> ".
　　　　　　　　　"<td>{$row['submission_date']} </td> ".
　　　　　　　　　"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

The output result is shown as follows:

MySQL GROUP BY StatementMySQL UNION Operator