Business Benefits
Retrieve or display the data in a more structured fashion from one table or by joining multiple tables.
Add the ORDER BY clause to the SELECT statement to sort data.
The following example illustrates the syntax for the ORDER BY clause. Here, the ORDER BY clause is used to specify two columns by which the data will be sorted:
SELECT
column(s)
FROM
table_name
ORDER BY
column1,
column2
```You can then create a table and import sample data into it using the MySQL workbench:
Todo_id Task. Priority
1000 Create docs. 3
1001 Upload file. 1
1002 Create table. 2
```
CREATE TABLE IF NOT EXISTS checklists (
todo_id INT AUTO_INCREMENT,
todo VARCHAR(255) NOT NULL,
priority INT
);
```
```Using the `ORDER BY` clause would output the tasks based on their priority:
```
```
select task, priority from checklists
order by priority
Output
Task. Priority
Upload file. 1
Create table. 2
Create docs. 3
```
```
## Sort data by ascending order using `ASC` or descending order using `DESC` with the `ORDER BY` clause.
For example:
```
```
select task, priority from checklists
order by priority ASC
Output
Task. Priority
Upload file. 1
Create table. 2
Create docs. 3
```
select task, priority from checklists
order by priority DESC
Output
Task. Priority
Create docs. 3
Create table. 2
Upload file. 1
## Use the MySQL `ORDER BY` clause combined with an expression to sort data by the result of a calculation.
For example, the data in the orders table below can be sorted in descending order based on the `price` by combining `ORDER BY` with the expression `quantityOrdered * priceEach` and `DESC`:
SELECT
orderNumber,
orderlinenumber,
quantityOrdered * priceEach
FROM
orderdetails
ORDER BY
quantityOrdered * priceEach DESC;
## Combine the `ORDER BY` clause with the `FIELD()` function to sort data using a custom list.
Take the following ***Sample Orders*** table from the sample database as an example:
ORDERS
order_number
order_date
shipped_date
status
customer_number
- In Process
- On Hold
- Canceled
- Shipped
You can use the `FIELD()` function to map each order status to a number and sort the result by that number.
SELECT
order_number,
status
FROM
orders
ORDER BY
FIELD(status,
'In Process',
'On Hold',
'Canceled',
'Shipped');
## Use the `SORT` and `JOIN` functions to sort data from multiple tables.
For instance, take the following tables:
ORDERS
order_id
order_date
order_amount
status
customer_id
CUSTOMERS
customer_id
customer_name
customer_city
```You can join these two tables and sort data based on one of the columns in a particular table. For example, to display `customer_name` and `order_amount` and sort by `order_amount` in descending order based on the ***ORDERS*** table, you would use this code:
```
```
SELECT
cust.customer_name,
ord.order_amount,
ord.status
FROM
customers cust,
orders ord
WHERE
cust.customer_id = ord.customer_id
ORDER BY ord.order_amount DESC
```
```The output lists all the customer names and amount they ordered in descending order. Note: `ord` and `cust` are the aliases for the respective tables.Last edited by @hesh_fekry 2023-11-14T11:08:03Z