MySQL Tutorial

MySQL Installation and Configuration

MySQL Database Operations

Database Design

MySQL Data Types

MySQL Storage Engines

MySQL Basic Operations of Tables

MySQL Constraints

MySQL Operators

MySQL Function

MySQL Function Overview

MySQL Manipulate Table Data

MySQL View

MySQL Indexes

MySQL Stored Procedure

MySQL Trigger

MySQL Transactions

MySQL Character Set

MySQL User Management

MySQL Database Backup and Recovery

MySQL Log

MySQL Performance Optimization

MySQL ORDER BY: sort query results

The ORDER BY keyword in MySQL is used to sort the result-set by one or more columns. It sorts the records in ascending order by default. To sort the records in descending order, you can use the DESC keyword.

Basic Usage

Consider a table named employees:

id	name	salary
1	John	3000
2	Alice	4000
3	Bob	3500

If you want to order the employees by salary in ascending order (from lowest to highest), you would do:

SELECT * FROM employees ORDER BY salary;

Descending Order

If you want to order the employees by salary in descending order (from highest to lowest), you would do:

SELECT * FROM employees ORDER BY salary DESC;

Multiple Columns

You can also sort by more than one column. The result set is sorted by the first column and then that sorted result set is sorted by the second column, and so on. For example, if you want to sort by salary in descending order, and then by name in ascending order, you would do:

SELECT * FROM employees ORDER BY salary DESC, name;

Important Notes

If ORDER BY is not specified, the order of the rows returned by a SELECT query is not guaranteed, even if the table has a primary key.
ORDER BY clause can be used in conjunction with LIMIT to retrieve a specific range of rows in a certain order.
Be mindful when using ORDER BY, especially on large datasets, as it can have a significant impact on performance. MySQL may need to create a temporary table and sort it, which can be slow for large tables.

For example, to retrieve the top 5 highest-paid employees, you can use ORDER BY with LIMIT like this:

SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

MySQL ORDER BY Clause Example:
- Description: The ORDER BY clause is used to sort the result set of a query based on one or more columns.
- Example Code:
```
SELECT * FROM table_name
ORDER BY column_name;
```
How to Use ORDER BY in MySQL:
- Description: Use ORDER BY to arrange query results in ascending order by default.
- Example Code:
```
SELECT product_name, price FROM products
ORDER BY product_name;
```
Sorting Query Results in MySQL:
- Description: ORDER BY is essential for sorting results, making them more readable.
- Example Code:
```
SELECT * FROM employees
ORDER BY hire_date DESC;
```
Sorting by Multiple Columns in MySQL:
- Description: Sort by multiple columns, specifying the order for each.
- Example Code:
```
SELECT * FROM transactions
ORDER BY transaction_date DESC, amount;
```
Sorting in Ascending vs Descending Order in MySQL:
- Description: Use ASC (default) or DESC to control the sorting order.
- Example Code:
```
SELECT * FROM customers
ORDER BY last_name ASC, first_name ASC;
```
Null Values and ORDER BY in MySQL:
- Description: Null values can be sorted differently. Use NULLS FIRST or NULLS LAST to control their placement.
- Example Code:
```
SELECT * FROM inventory
ORDER BY expiry_date NULLS LAST;
```
Custom Sorting with ORDER BY in MySQL:
- Description: You can use expressions or functions for custom sorting.
- Example Code:
```
SELECT * FROM products
ORDER BY CASE WHEN stock_quantity < 10 THEN 1 ELSE 0 END, stock_quantity DESC;
```
Examples of Using ORDER BY in MySQL Queries:
- Description: Various examples demonstrating the versatility of ORDER BY in different scenarios.
- Example Code:
```
SELECT * FROM tasks
ORDER BY priority DESC, due_date;
```