We know how to use SQL SELECT statements to read data from MySQL tables.

If we need to sort the data we read, we can use MySQL’s ORDER BY clause to specify which fields you want to sort BY and how you want to return the search results.

grammar

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

SELECT field1, field2,... fieldN FROM table_name1, table_name2... ORDER BY field1 [ASC][default ASC]], field2... [ASC [DESC][default ASC]]Copy the code
  • You can use any field as a sort condition to return the 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… The LIKE clause sets the condition.

Use the ORDER BY clause in the command prompt

SELECT * from runoob_tbl; SELECT * from runoob_tbl;

The instance

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

SQL sort

mysql> use RUNOOB; Database changed mysql> SELECT * from runoob_tbl ORDER BY submission_date ASC; +———–+—————+—————+—————–+ | runoob_id | runoob_title | runoob_author | Submission_date | + — — — — — — — — — — – + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — — — — + | 3 | learn Java | | RUNOOB.COM 2015-05-01 | | | 4 learning Python | | 2016-03-06 | RUNOOB.COM | 1 | | learning PHP beginners tutorial | 2017-04-12 | | 2 | learn MySQL rookie tutorial | | The 2017-04-12 | + — — — — — — — — — — – + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — — — – + 4 rows in the set (0.01 SEC) mysql > SELECT * from runoob_tbl ORDER BY submission_date DESC; +———–+—————+—————+—————–+ | runoob_id | runoob_title | runoob_author | Submission_date | + — — — — — — — — — — – + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — — — — + | | | 1 learning PHP beginners tutorial | 2017-04-12 | | 2 | learn MySQL rookie tutorial | 2017-04-12 | | | | 4 learning Python | | 2016-03-06 | RUNOOB.COM | 3 | learn Java | | 2015-05-01 | RUNOOB.COM + — — — — — — — — — — – + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — — — – + 4 rows in the set (0.01 SEC)

Read all data in the RUNOob_TBL table in ascending order of submission_date.

Use the ORDER BY clause in PHP scripts

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

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

The instance

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

MySQL ORDER BY

<? php $dbhost = ‘localhost’; $dbuser = ‘root’; $dbpass = ‘123456’; $conn = mysqli_connect($dbuser, $dbpass); if(! $conn) {die(‘ connection failed: ‘. Mysqli_error ($conn)); Mysqli_query ($conn, “set names utf8”); $sql = ‘SELECT runoob_id, runoob_title, runoob_author, submission_date FROM runoob_tbl ORDER BY submission_date ASC’; mysqli_select_db( $conn, ‘RUNOOB’ ); $retval = mysqli_query( $conn, $sql ); if(! $retval) {die(‘ cannot read data: ‘.mysqli_error ($conn)); } echo ‘<h2> MySQL ORDER BY test <h2>; Echo ‘< table border = “1” > < tr > < td > tutorial ID < / td > < td > title < / td > < / td > < td > the author submit date < / td > < td > < / tr >’; while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC)) { echo “<tr><td> {$row[‘runoob_id’]}</td> “. “<td>{$row[‘runoob_title’]} </td> “. “<td>{$row[‘runoob_author’]} </td> “. “<td>{$row[‘submission_date’]} </td> “. “</tr>”; } echo ‘</table>’; mysqli_close($conn); ? >

The output result is as follows: