The ORDER BY clause in MySQL is used to sort the result set of a query based on one or more columns. Sorting can be done in ascending or descending order, allowing you to organize your query results in a meaningful way. This clause is useful for improving readability and for performing tasks where the order of data is important, such as generating reports or displaying data to users.

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

• column1, column2, ...: The columns by which to sort the results.
• ASC: Sorts the results in ascending order (this is the default).
• DESC: Sorts the results in descending order.

Examples

1. Basic Sorting

   To sort results by a single column in ascending order:

   SELECT first_name, last_name
   FROM employees
   ORDER BY last_name;
   

   This query retrieves employee names and sorts them alphabetically by last_name.

2. Descending Order

   To sort results in descending order:

   SELECT first_name, salary
   FROM employees
   ORDER BY salary DESC;
   

   This query retrieves employee names and salaries, sorting them so that the highest salary appears first.

3. Multiple Columns

   To sort by multiple columns, specify the columns and their sort orders:

   SELECT first_name, last_name, department
   FROM employees
   ORDER BY department, last_name;
   

   This query sorts employees first by department in ascending order and then by last_name within each department.

4. Combining Ascending and Descending Orders

   To sort by one column in ascending order and another in descending order:

   SELECT first_name, last_name, salary
   FROM employees
   ORDER BY department ASC, salary DESC;
   

   This query sorts employees by department in ascending order and then by salary in descending order within each department.

5. Using Aliases

   When using column aliases, you can sort by the alias:

   SELECT first_name, last_name, salary AS employee_salary
   FROM employees
   ORDER BY employee_salary DESC;
   

   This query sorts employees by their employee_salary in descending order.

6. Sorting with Expressions

   You can also sort by expressions or calculations:

   SELECT first_name, last_name, salary, (salary * 0.1) AS bonus
   FROM employees
   ORDER BY bonus DESC;
   

   This query calculates a bonus for each employee and sorts the results by this calculated bonus in descending order.

Best Practices

• Indexing: For better performance, especially with large datasets, ensure that columns used in ORDER BY are indexed.
• Limit Result Set: Use LIMIT in combination with ORDER BY to restrict the number of rows returned, such as getting the top N records.
• Use Aliases: When sorting by complex expressions or calculations, consider using column aliases to make your queries more readable.
• Combine with Filtering: Use WHERE clauses to filter data before sorting to get meaningful and relevant results.