-- Learning is not just about technology, but also about dreams!
Home HTML JAVASCRIPT CSS VUE REACT PYTHON3 JAVA C C++ C# AI GO SQL LINUX VS CODE BOOTSTRAP GIT Local Bookmarks
MySQL Tutorial
MySQL Tutorial
MySQL Installation
MySQL Management
MySQL Connection
MySQL Create Database
MySQL Drop Database
MySQL Select Database
MySQL Data Types
MySQL Create Table
MySQL Drop Table
MySQL Insert Data
MySQL Query Data
MySQL WHERE Clause
MySQL UPDATE
MySQL DELETE Statement
MySQL LIKE Clause
MySQL UNION
MySQL ORDER BY Statement
MySQL Group By
MySQL Join
MySQL NULL Values
MySQL Regular Expressions
MySQL Transactions
MySQL ALTER Command
MySQL Indexes
MySQL Temporary Tables
MySQL Copy Tables
MySQL Metadata
MySQL Sequences
MySQL Handling Duplicates
MySQL and SQL Injection
MySQL Export Data
MySQL Import Data
MySQL Functions
MySQL Operators
MySQL Commands
MySQL Quiz
MySQL Programming Languages
MySQL Node.js
MySQL PHP
MySQL Python
MySQL Java
MySQL UNION Statement
MySQL Group by Statement
Explore Further
Programming
Web Services
Programming Languages
Software
Web Design & Development
Scripts
Development Tools
Scripting Languages
Web Service
Computer Science
MySQL ORDER BY (Sorting) Statement
We know that we use the SELECT statement 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 field and in what manner you want to sort, and then return the search results.
The MySQL ORDER BY (Sorting) statement can sort data in ascending (ASC) or descending (DESC) order based on the values of one or more columns.
Syntax
The following is the SELECT statement using the ORDER BY clause to sort the queried data before returning it:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;
Parameter Description:
column1, column2, ... are the names of the columns you want to select. Use * to select all columns.
table_name is the name of the table from which you want to query data.
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ... is the clause used to specify the sort order. ASC indicates ascending order (default), and DESC indicates descending order.
Further Explanation:
You can use any field as the sorting condition to return sorted query results.
You can set multiple fields for sorting.
You can use the 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.
Examples
Here are some examples of using the ORDER BY clause.
1. Single Column Sort:
SELECT * FROM products
ORDER BY product_name ASC;
The above SQL statement will select all products from the products table and sort them by product_name in ascending (ASC) order.
2. Multi-Column Sort:
SELECT * FROM employees
ORDER BY department_id ASC, hire_date DESC;
The above SQL statement will select all employees from the employees table, first sort by department_id in ascending (ASC) order, and then within the same department, sort by hire_date in descending (DESC) order.
3. Using Numbers to Represent Column Position:
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC, 1 ASC;
The above SQL statement will select the first_name and salary columns from the employees table, sort by the third column (salary) in descending (DESC) order, and then by the first column (first_name) in ascending (ASC) order.
4. Sorting Using an Expression:
SELECT product_name, price * discount_rate AS discounted_price
FROM products
ORDER BY discounted_price DESC;
The above SQL statement will select the product_name and the discounted price calculated based on the discount rate from the products table, and sort by the discounted_price in descending (DESC) order.
5. Starting from MySQL version 8.0.16, you can use NULLS FIRST or NULLS LAST to handle NULL values:
SELECT product_name, price
FROM products
ORDER BY price DESC NULLS LAST;
The above SQL statement will select the product_name and price from the products table, sort by price in descending (DESC) order, and place NULL values last.
Conversely, if you want NULL values to appear first, you can write it like this:
SELECT product_name, price
FROM products
ORDER BY price DESC NULLS FIRST;
The ORDER BY clause is a powerful tool that can sort query results based on different business requirements. In practical applications, pay attention to selecting appropriate columns and sort orders to achieve the desired sorting effect.
Using the ORDER BY Clause in the Command Prompt
The following will use the ORDER BY clause in a SELECT statement to read data from the MySQL table tutorial_tbl:
Example
Try the following examples, the results will be sorted in ascending and descending order.
SQL Sort
mysql> use ;
Database changed
mysql> SELECT * from tutorial_tbl ORDER BY submission_date ASC;
+-----------+---------------+---------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3 | Learn Java | .COM | 2015-05-01 |
| 4 | Learn Python | .COM | 2016-03-06 |
| 1 | Learn PHP | 2017-04-12 |
| 2 | Learn MySQL | 2017-04-12 |
+-----------+---------------+---------------+-----------------+
4 rows in set (0.01 sec)
mysql> SELECT * from tutorial_tbl ORDER BY submission_date DESC;
+-----------+---------------+---------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1 | Learn PHP | 2017-04-12 |
| 2 | Learn MySQL | 2017-04-12 |
| 4 | Learn Python | .COM | 2016-03-06 |
| 3 | Learn Java | .COM | 2015-05-01 |
+-----------+---------------+---------------+-----------------+
4 rows in set (0.01 sec)
Read all data from the tutorial_tbl table and sort it in ascending order by the submission_date field.
Using the ORDER BY Clause in a PHP Script
You can use the PHP function mysqli_query() and the same SELECT command with the ORDER BY clause to fetch data.
This function is used to execute the SQL command, and then the PHP function mysqli_fetch_array() is used to output all the queried data.
Example
Try the following example, the queried data will be returned sorted in descending order by the submission_date field.
MySQL ORDER BY Test:
<?php
$dbhost = 'localhost'; // MySQL server host address
$dbuser = 'root'; // MySQL username
$dbpass = '123456'; // MySQL user password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese garbled text
mysqli_query($conn , "set names utf8");
$sql = 'SELECT tutorial_id, tutorial_title,
tutorial_author, submission_date
FROM tutorial_tbl
ORDER BY submission_date ASC';
mysqli_select_db( $conn, '' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('Cannot read data: ' . mysqli_error($conn));
}
echo '
MySQL ORDER BY Test';
echo '
| Tutorial ID | Title | Author | Submission Date |
';
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))
{
echo "| {$row['tutorial_id']} | ".
"{$row['tutorial_title']} | ".
"{$row['tutorial_author']} | ".
"{$row['submission_date']} | ".
"
";
}
echo '
';
mysqli_close($conn);
?>
The output result is shown in the following image:
MySQL UNION Statement
MySQL Group by Statement
ByteArk Coding Plan
Supports mainstream large models like Doubao, GLM, DeepSeek, Kimi, MiniMax, officially supplied, stable and reliable.
Configuration Guide
¥9.9
/ month
Activate Now
iFlytek Spark Coding Plan
Includes free model call quota, DeepSeek, GLM, Kimi, MiniMax, one-stop experience and deployment platform.
Configuration Guide
¥3.9
/ month
Activate Now
1 Note
Write Note
argyi
150***62378@163.com
319
MySQL Sorting We know that we use the SQL SELECT statement to read from MySQL tables:
MySQL Pinyin Sorting
If the character set is gbk (Chinese character encoding character set), simply add ORDER BY after the query statement:
SELECT *
FROM tutorial_tbl
ORDER BY tutorial_title;
If the character set is utf8 (Unicode), you need to convert the field first and then sort:
SELECT *
FROM tutorial_tbl
ORDER BY CONVERT(tutorial_title using gbk);
argyi
argyi
150***62378@163.com
8 years ago (2018-05-23)
Category Navigation
Python / Data Science
AI / Intelligent Development
Frontend Development
Backend Development
Database
Mobile Development
DevOps / Engineering
Programming Languages
Computer Basics
XML / Web Service
.NET
Website Building
Advertisement
MySQL Tutorial
MySQL Tutorial
MySQL Installation
MySQL Management
MySQL Connection
MySQL Create Database
MySQL Drop Database
MySQL Select Database
MySQL Data Types
MySQL Create Table
MySQL Drop Table
MySQL Insert Data
MySQL Query Data
MySQL WHERE Clause
MySQL UPDATE
MySQL DELETE Statement
MySQL LIKE Clause
MySQL UNION
MySQL ORDER BY Statement
MySQL Group By
MySQL Join
MySQL NULL Values
MySQL Regular Expressions
MySQL Transactions
MySQL ALTER Command
MySQL Indexes
MySQL Temporary Tables
MySQL Copy Tables
MySQL Metadata
MySQL Sequences
MySQL Handling Duplicates
MySQL and SQL Injection
MySQL Export Data
MySQL Import Data
MySQL Functions
MySQL Operators
MySQL Commands
MySQL Quiz
MySQL Programming Languages
MySQL Node.js
MySQL PHP
MySQL Python
MySQL Java
Online Examples
·HTML Examples
·CSS Examples
·JavaScript Examples
·Ajax Examples
·jQuery Examples
·XML Examples
·Java Examples
Character Sets & Tools
· HTML Character Set Settings
· HTML ASCII Character Set
· JS Obfuscation/Encryption
· PNG/JPEG Image Compression
· HTML Color Picker
· JSON Formatter
· Random Number Generator
Latest Updates
· AI Ethics and Safety
· How AI Works
· AI Tools Panorama
· Prompt
· Introduction to AI
· AI Beginner Tutorial
· Large Model Multimodal (M...
Site Information
· Feedback
· Disclaimer
· About Us
· Article Archive
Follow WeChat
My Favorites
Mark Article
Browsing History
Clear All
No records yet
YouTip