Sort data in MySQL
Sort data in MySQL
1. 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
2. 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
3. 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; This would output the following:
4. 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 If the output needs to be sorted based on sales orders and their statuses in the following order: 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');
5. 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.