SQL ORDER BY Tutorial

The ORDER BY clause in SQL is used to sort the result set of a query by one or more columns. It can sort the data in ascending (ASC) or descending (DESC) order. If no order is specified, the default is ascending.

Syntax

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

Examples

Sorting by a Single Column

To sort the results by a single column, you can use the following query:

SELECT * FROM employees
ORDER BY last_name ASC;

This query will return all employees sorted by their last name in ascending order.

Sorting by Multiple Columns

You can also sort by multiple columns. For example:

SELECT * 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.

Sorting with NULL Values

When sorting, NULL values are considered the lowest possible values. To handle NULL values differently, you can use NULLS FIRST or NULLS LAST in some SQL dialects like PostgreSQL:

SELECT * FROM employees
ORDER BY commission_pct NULLS LAST;

Best Practices

1. Indexing: Ensure that columns used in ORDER BY are indexed for better performance.
2. Limit Results: Use LIMIT or FETCH FIRST to limit the number of rows returned, especially when sorting large datasets.
3. Avoid Sorting Unnecessarily: Sorting can be resource-intensive, so avoid it if the order of results is not important.

Conclusion

The ORDER BY clause is a powerful tool in SQL for organizing query results. By mastering its use, you can ensure that your data is presented in a meaningful and efficient manner.