MySQL sorting

MySQL sorting

We know that SQL SELECT statements are used to read data from mySQL tables.

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

Database structure and data downloads used in this section: MySQL .sql

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, [field2...] [ASC [DESC]]

• You can use any field as a condition for sorting, returning the results of the sorted query.
• You can set up multiple fields to sort.
• You can use the ASC or DESC keywords to set query results in ascending or descending order. By default, it is arranged in liters.
• You can add WHERE... LIKE clause to set the condition.

Use the ORDER BY clause in command prompts

The following will use the ORDER BY clause in SQL SELECT statements to read the data in the MySQL w3cschool_tbl data table:

Instance

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

root@host# mysql -u root -p password;
Enter password:
mysql> use W3CSCHOOL;
Database changed
mysql> SELECT  from w3cschool_tbl ORDER BY w3cschool_author ASC;
+-------------+----------------+-----------------+-----------------+
| w3cschool_id | w3cschool_title | w3cschool_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           2 | Learn MySQL    | Abdul S         | 2007-05-24      |
|           1 | Learn PHP      | John Poul       | 2007-05-24      |
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-06      |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.00 sec)

mysql> SELECT * from w3cschool_tbl ORDER BY w3cschool_author DESC;
+-----------+------------------+-----------------+-----------------+
| w3cschool_id | w3cschool_title | w3cschool_author | submission_date |
+-----------+------------------+-----------------+-----------------+
|         3 | JAVA Tutorial    | Sanjay          | 2007-05-06      |
|         1 | Learn PHP        | John Poul       | 2007-05-24      |
|         2 | Learn MySQL      | Abdul S         | 2007-05-24      |
3 rows in set (0.00 sec)

mysql>

Read w3cschool_tbl data in the table and arrange it w3cschool_author descending order of the field.

Use the ORDER BY clause in PHP scripts

Instance

Try the following example, and the data after the query w3cschool_author in descending order of the field.

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT w3cschool_id, w3cschool_title,
               w3cschool_author, submission_date
        FROM w3cschool_tbl
        ORDER BY  w3cschool_author DESC';

mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Tutorial ID :{$row['w3cschool_id']}




 ".
         "Title: {$row['w3cschool_title']} 
 ".
         "Author: {$row['w3cschool_author']} 
 ".
         "Submission Date : {$row['submission_date']} 
 ".
         "--------------------------------
";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>