MySQL ORDER BY

MySQL ORDER BY Clause

The ORDER BY clause in MySQL is used to sort the result set of a query in either ascending (ASC) or descending (DESC) order. By default, the sorting order is ascending. You can sort by one or more columns.

Syntax

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

• column_name: The column(s) by which the result should be sorted.
• ASC: Sorts in ascending order (default).
• DESC: Sorts in descending order.

1. Sort by a Single Column

Example

Sort the employees table by name in ascending order:

SELECT id, name, position, salary
FROM employees
ORDER BY name ASC;

Result

+----+------------+-----------+--------+
| id | name       | position  | salary |
+----+------------+-----------+--------+
| 1  | Alice      | Developer | 60000  |
| 2  | Bob        | Manager   | 80000  |
| 3  | Charlie    | Analyst   | 50000  |
+----+------------+-----------+--------+

2. Sort by Multiple Columns

You can sort by multiple columns, specifying the order for each.

Example

Sort employees by position in ascending order and, within the same position, by salary in descending order:

SELECT id, name, position, salary
FROM employees
ORDER BY position ASC, salary DESC;

Result

+----+------------+-----------+--------+
| id | name       | position  | salary |
+----+------------+-----------+--------+
| 3  | Charlie    | Analyst   | 50000  |
| 1  | Alice      | Developer | 60000  |
| 2  | Bob        | Manager   | 80000  |
+----+------------+-----------+--------+

3. Sorting by an Expression

You can sort using calculated expressions or derived values.

Example

Sort by salary increased by 10%:

SELECT id, name, salary, (salary * 1.10) AS adjusted_salary
FROM employees
ORDER BY adjusted_salary DESC;

4. Sorting by Aliases

You can use an alias in the ORDER BY clause, but it must appear in the SELECT statement.

Example

SELECT id, name, salary, (salary * 1.10) AS adjusted_salary
FROM employees
ORDER BY adjusted_salary ASC;

5. Sorting by Column Index

You can also sort by the column index (position of the column in the SELECT list).

Example

Sort by the second column (name):

SELECT id, name, position, salary
FROM employees
ORDER BY 2 ASC;

6. Using NULL Values in Sorting

• By default, NULL values are sorted first in ascending order and last in descending order.

Example

SELECT id, name, salary
FROM employees
ORDER BY salary ASC;

Result

+----+--------+--------+
| id | name   | salary |
+----+--------+--------+
| 4  | David  | NULL   |
| 3  | Charlie| 50000  |
| 1  | Alice  | 60000  |
+----+--------+--------+

7. Practical Example

Table: products

+----+-------------+-------+--------+
| id | product_name| stock | price  |
+----+-------------+-------+--------+
| 1  | Laptop      | 50    | 1500.0 |
| 2  | Smartphone  | 200   | 800.0  |
| 3  | Tablet      | 100   | 300.0  |
| 4  | Monitor     | 150   | 200.0  |
+----+-------------+-------+--------+

1. Sort by Price Descending

   SELECT * FROM products
   ORDER BY price DESC;
   

2. Sort by Stock Ascending and Price Descending

   SELECT * FROM products
   ORDER BY stock ASC, price DESC;
   

8. Common Errors

1. Invalid Column Name: If the column specified in ORDER BY does not exist, you will get an error:

   ERROR 1054 (42S22): Unknown column 'non_existing_column' in 'order clause'
   

2. Using ORDER BY in UNION: When using UNION, the ORDER BY must refer to columns in the final result set.

9. Best Practices

1. Indexing: Add indexes to columns frequently used in ORDER BY for better performance.
2. Avoid Sorting Large Data Sets: Use limits (LIMIT) to restrict the number of rows being sorted.

Let me know if you need further clarification or examples!