In SQL, the ORDER BY clause sorts query results based on specified columns or expressions. It is added at the end of a SELECT statement and allows you to control the sequence of rows in the output. By default, sorting is done in ascending order (from A-Z or 0-9), but you can explicitly define the direction using ASC (ascending) or DESC (descending). For example, SELECT name, salary FROM employees ORDER BY salary DESC; retrieves employee names and salaries, sorted from highest to lowest. This basic usage works for both numerical and textual data, making it straightforward to organize results predictably.
You can sort by multiple columns by listing them sequentially in the ORDER BY clause. The database processes the columns in the order they appear, refining the sort step-by-step. For instance, SELECT department, name, hire_date FROM employees ORDER BY department ASC, hire_date DESC; first sorts employees by department alphabetically, then within each department, orders them by hire date (newest first). This hierarchical approach is useful for grouping related data while maintaining a secondary sort criterion. Note that each column can have its own sort direction—e.g., ORDER BY department DESC, name ASC sorts departments Z-A but names A-Z.
Advanced sorting includes expressions or functions. For example, SELECT product_name FROM products ORDER BY LENGTH(product_name); sorts products by the length of their names. You can also reference columns by their position in the SELECT list (e.g., ORDER BY 2 sorts by the second column). However, positional references can reduce readability. Performance-wise, sorting large datasets can be slow, especially without indexes. While ORDER BY is essential for organized output, avoid unnecessary sorting in queries handling millions of rows. If frequent sorting is required on specific columns, consider adding indexes to those columns to improve efficiency.