The ORDER BY clause in SQL is used to sort the result set of a query in either ascending or descending order. Sorting data helps you organize and analyze it more effectively, whether you're working with a small table or large datasets.
The ORDER BY clause is placed at the end of a SQL query. You can specify one or more columns to sort by, and you can choose whether to sort in ascending or descending order.
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
The ASC keyword stands for "ascending" order (which is the default), and the DESC keyword stands for "descending" order.
By default, when you use ORDER BY without specifying ASC, the results will be sorted in ascending order. Ascending order arranges the values from lowest to highest (e.g., A-Z, 1-9).
-- Select all employees and sort them by age in ascending order
SELECT Name, Age
FROM Employees
ORDER BY Age ASC;
This query retrieves the 'Name' and 'Age' of employees, sorted by the 'Age' column from the lowest to the highest value. The ASC keyword is optional because ascending is the default sorting order.
To sort the results in descending order (from highest to lowest), you need to use the DESC keyword. This is useful when you want to display the largest or most recent values first.
-- Select all employees and sort them by salary in descending order
SELECT Name, Salary
FROM Employees
ORDER BY Salary DESC;
This query retrieves the 'Name' and 'Salary' of employees, sorted by the 'Salary' column from highest to lowest.
You can sort the results by multiple columns. If there are rows with the same value in the first column, SQL will sort them based on the second column, and so on. Sorting by multiple columns is helpful when you need to organize data in more complex ways.
-- Select all employees and sort by department and then by salary
SELECT Name, Department, Salary
FROM Employees
ORDER BY Department ASC, Salary DESC;
This query retrieves the 'Name', 'Department', and 'Salary' of employees. First, it sorts the data by 'Department' in ascending order. For rows that have the same department, it sorts them by 'Salary' in descending order.
SQL treats NULL values as unknown, and the sorting behavior of NULL values can differ between databases. By default, in most databases, NULL values are sorted at the beginning of the result set when using ASC, and at the end when using DESC.
-- Select employees and sort by salary, with NULL values last
SELECT Name, Salary
FROM Employees
ORDER BY Salary DESC NULLS LAST;
This query retrieves the 'Name' and 'Salary' of employees, sorting by 'Salary' in descending order. The NULLS LAST keyword ensures that any rows with a NULL value in the 'Salary' column appear last in the result set.
Sorting by date fields follows the same principle as sorting by other types of data. Dates are sorted in chronological order (earliest to latest for ascending, latest to earliest for descending).
-- Select all orders and sort them by order date in ascending order
SELECT OrderID, OrderDate
FROM Orders
ORDER BY OrderDate ASC;
This query retrieves the 'OrderID' and 'OrderDate' of all orders, sorted by the 'OrderDate' column from the earliest to the most recent date.
When sorting textual data, SQL is typically case-insensitive by default in many systems. However, this behavior can vary based on the collation settings of your database. In some cases, you might need to explicitly specify case-insensitive sorting.
-- Select all customers and sort their names in case-insensitive order
SELECT Name
FROM Customers
ORDER BY Name COLLATE utf8_general_ci;
This query retrieves the 'Name' of all customers, sorting them in a case-insensitive manner by using a specific collation.
If you do not use the ORDER BY clause, the database may return rows in an arbitrary order. This can vary each time you run the query, depending on how the database engine processes the query.
-- Select all products without any specific order
SELECT ProductName, Price
FROM Products;
This query retrieves all products without any particular sorting. The order of the results is not guaranteed.
The ORDER BY clause is a valuable tool for sorting the results of a query in SQL. Whether you're working with numbers, text, dates, or other data types, sorting helps you present your results in a meaningful and organized way. By understanding how to use ORDER BY, you can enhance the clarity and usefulness of your query results.