In MySQL, The ORDER BY clause is used to sort the result set of a query based on one or more columns.
We can specify the column(s) by which we can order the results and whether the sorting should be in ascending (default) or descending order.
By Default, It Sorts the result set in `ASCENDING` Order.
Here is the basic syntax of the ORDER BY clause:
SELECT column1, column2 FROM tableName ORDER BY column1 [ASC | DESC], column2 [ASC | DESC];
column1, column2: The columns we want to retrieve from the table.
tableName: The name of the table from which we want to retrieve data.
ORDER BY: Specifies the column(s) by which to sort the result set.
ASC: Ascending order (default).
DESC: Descending order.
The below query retrieves all columns from the `products` table and sorts the result set in ascending order based on the "product_name" column.
SELECT * FROM products ORDER BY product_name;
The below query retrieves all columns from the products table and sorts the result set in descending order based on the price column.
SELECT * FROM products ORDER BY price DESC;
The below query retrieves all columns from the "orders" table and sorts the result set first in descending order based on the "order_date" column and then in ascending order based on the "total_amount" column.
SELECT * FROM orders ORDER BY order_date DESC, total_amount ASC;
We can use aliases for columns when sorting the columns and it is useful when performing calculations.
SELECT product_name, product_code, product_price + delivery_charges AS total_price_of_product FROM products ORDER BY total_price_of_product ASC;
The above query will retrieve the result set with columns such as `product_name`, `product_code`, and `total_price_of_product` in Ascending order.
We can use the `LIMIT` clause to restrict the number of records retrieved from the table and sort the columns using the `ORDER BY` clause can help you retrieve the records sets from the table.
SELECT product_name, product_code, product_price FROM products ORDER BY product_price ASC LIMIT 20;