When you retrieve data using the SELECT statement, the database returns rows in an order it deems efficient, which often appears arbitrary. It might be the order the data was inserted, or it might depend on how the data is stored internally. However, frequently, you need to present this data in a specific sequence, such as alphabetically by name, numerically by price, or chronologically by date.

This is where the ORDER BY clause comes in. It allows you to specify one or more columns by which the result set should be sorted. You add the ORDER BY clause at the end of your SELECT statement.

Basic Sorting: Ascending Order

The simplest form of sorting is in ascending order (A to Z, 0 to 9). This is the default behavior of ORDER BY.

Let's imagine we have a Products table like this:

ProductID	ProductName	Price
1	Laptop	1200
2	Mouse	25
3	Keyboard	75
4	Monitor	300
5	Webcam	50

If you run a simple SELECT * FROM Products; query, the order isn't guaranteed. To sort these products alphabetically by ProductName, you would use:

SELECT ProductID, ProductName, Price
FROM Products
ORDER BY ProductName;

This query tells the database to retrieve all products and then arrange the results based on the values in the ProductName column before returning them to you. Because ascending order is the default, you don't explicitly need to state it. The output would look like this:

ProductID	ProductName	Price
3	Keyboard	75
1	Laptop	1200
4	Monitor	300
2	Mouse	25
5	Webcam	50

You can explicitly specify ascending order using the ASC keyword, although it's optional since it's the default:

SELECT ProductID, ProductName, Price
FROM Products
ORDER BY ProductName ASC; -- ASC is optional here

This produces the exact same result as the previous query.

Sorting in Descending Order

What if you want to see the products listed from the most expensive to the least expensive? For this, you need to sort in descending order (Z to A, 9 to 0). You achieve this by adding the DESC keyword after the column name in the ORDER BY clause.

SELECT ProductID, ProductName, Price
FROM Products
ORDER BY Price DESC;

This query sorts the results based on the Price column, arranging them from the highest price to the lowest:

ProductID	ProductName	Price
1	Laptop	1200
4	Monitor	300
3	Keyboard	75
5	Webcam	50
2	Mouse	25

Sorting by Multiple Columns

Sometimes, sorting by a single column isn't sufficient. Consider an Employees table:

EmployeeID	FirstName	LastName	Department	Salary
101	Alice	Smith	Sales	60000
102	Bob	Johnson	Engineering	75000
103	Charlie	Smith	Engineering	80000
104	David	Williams	Sales	55000
105	Eve	Brown	Engineering	70000

If you sort only by Department, employees within the same department will appear in an undefined order. To create a more organized list, you might want to sort first by Department alphabetically, and then within each department, sort by LastName alphabetically.

You can achieve this by listing multiple columns in the ORDER BY clause, separated by commas. The database sorts by the first column listed, then for rows where the first column's value is the same, it sorts by the second column, and so on.

SELECT EmployeeID, FirstName, LastName, Department
FROM Employees
ORDER BY Department ASC, LastName ASC;

You can omit ASC as it's the default, but we include it here for clarity. The result would be:

EmployeeID	FirstName	LastName	Department
105	Eve	Brown	Engineering
102	Bob	Johnson	Engineering
103	Charlie	Smith	Engineering
101	Alice	Smith	Sales
104	David	Williams	Sales

Notice how all 'Engineering' employees come before 'Sales'. Within 'Engineering', 'Brown' comes before 'Johnson', which comes before 'Smith'. Within 'Sales', 'Smith' comes before 'Williams'.

You can also mix ascending and descending order for different columns. For example, to sort by department alphabetically, but list employees by highest salary first within each department:

SELECT EmployeeID, FirstName, LastName, Department, Salary
FROM Employees
ORDER BY Department ASC, Salary DESC;

EmployeeID	FirstName	LastName	Department	Salary
103	Charlie	Smith	Engineering	80000
102	Bob	Johnson	Engineering	75000
105	Eve	Brown	Engineering	70000
101	Alice	Smith	Sales	60000
104	David	Williams	Sales	55000

Combining WHERE and ORDER BY

The ORDER BY clause is applied after the WHERE clause has filtered the rows. It sorts only the rows that meet the WHERE condition. The ORDER BY clause must always come after the WHERE clause in your SQL statement.

Example: Select all products with a price less than $100, ordered by name.

SELECT ProductID, ProductName, Price
FROM Products
WHERE Price < 100
ORDER BY ProductName ASC;

This query first finds all products where the Price is less than 100: Mouse (25), Keyboard (75), and Webcam (50). Then, it sorts these filtered results by ProductName:

ProductID	ProductName	Price
3	Keyboard	75
2	Mouse	25
5	Webcam	50

Data Types and Sorting

ORDER BY works intuitively with common data types:

Numbers: Sorted numerically (1, 2, 10, 20).
Strings (Text): Sorted alphabetically based on the database's collation rules (which define character order, case sensitivity, etc.). 'Apple' comes before 'Banana'. 'apple' might come before or after 'Banana' depending on case sensitivity settings.
Dates/Times: Sorted chronologically ('2023-01-01' comes before '2023-01-02').

A Note on Performance

While incredibly useful, sorting large result sets can consume significant database resources (CPU time and potentially memory or disk space for temporary storage). On tables with millions or billions of rows, an ORDER BY clause without appropriate indexing can make queries noticeably slower. This is a more advanced topic, but it's good to be aware that sorting isn't "free". For the datasets you'll typically encounter initially, the impact is usually negligible.

In summary, the ORDER BY clause gives you control over the presentation order of your query results. Use it with column names and optionally ASC (default) or DESC to sort your data meaningfully, making it easier to read and analyze. Remember it comes after the FROM and WHERE clauses in your SELECT statement.