Retrieve or display the data in a more structured fashion from one table or by joining multiple tables.
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:
```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;
!(https://cxl.com/institute/wp-content/uploads/2021/03/image-29.png) ## 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:
- 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:
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.