Sorting Results with ORDER BY in SQL

Table of Contents

1. Introduction
2. What is the ORDER BY Clause?
3. Basic Syntax of ORDER BY
4. Sorting in Ascending Order (ASC)
5. Sorting in Descending Order (DESC)
6. Default Sorting Behavior
7. Sorting by Multiple Columns
8. Sorting by Column Positions
9. Sorting by Aliased Columns
10. Sorting Text vs Numbers
11. Sorting by Date and Time
12. Sorting with NULL Values
13. Using ORDER BY with LIMIT
14. Combining ORDER BY with WHERE
15. Real-World Examples of Sorting
16. Performance Considerations
17. ORDER BY vs GROUP BY
18. Common Mistakes and How to Avoid Them
19. Best Practices for Sorting
20. Summary and What’s Next

1. Introduction

When working with SQL queries, the order in which rows are returned is not guaranteed unless you explicitly define it using the ORDER BY clause. Whether you’re building a leaderboard, a product list, or a list of recent transactions — ORDER BY is essential.

2. What is the ORDER BY Clause?

The ORDER BY clause in SQL is used to sort the result set returned by a SELECT query. You can sort data ascendingly or descendingly based on one or more columns.

3. Basic Syntax of ORDER BY

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

Example:

sqlCopyEditSELECT name, age FROM users
ORDER BY age;

4. Sorting in Ascending Order (ASC)

This is the default behavior:

sqlCopyEditSELECT name FROM employees
ORDER BY name ASC;

It sorts alphabetically (A–Z) or numerically (low to high).

5. Sorting in Descending Order (DESC)

Use DESC to sort in reverse order:

sqlCopyEditSELECT name, salary FROM employees
ORDER BY salary DESC;

This is useful for rankings, top-scorers, etc.

6. Default Sorting Behavior

If you do not specify a direction, SQL assumes ASC by default:

sqlCopyEditORDER BY age;  -- same as ORDER BY age ASC

7. Sorting by Multiple Columns

Sort first by one column, then by another:

sqlCopyEditSELECT * FROM users
ORDER BY city ASC, age DESC;

This sorts users by city alphabetically and then by age (older users first) within each city.

8. Sorting by Column Positions

You can also use column position numbers:

sqlCopyEditSELECT name, age, salary FROM employees
ORDER BY 3 DESC, 2 ASC;

This sorts by the 3rd column (salary), then 2nd column (age). While concise, this is less readable and more error-prone — use with caution.

9. Sorting by Aliased Columns

If you use AS to alias a column, you can sort by that alias:

sqlCopyEditSELECT name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

10. Sorting Text vs Numbers

• Strings sort lexicographically (alphabetically)
• Numbers sort mathematically

Example:

sqlCopyEditSELECT name, department FROM employees
ORDER BY department;

Departments are ordered alphabetically:
Accounting, Engineering, Finance, etc.

11. Sorting by Date and Time

sqlCopyEditSELECT * FROM orders
ORDER BY order_date DESC;

This is especially useful to get:

• Most recent orders
• Latest login
• Chronological events

12. Sorting with NULL Values

Different databases handle NULL differently:

• MySQL: NULL appears first with ASC, last with DESC
• PostgreSQL: allows NULLS FIRST or NULLS LAST explicitly:

sqlCopyEditSELECT * FROM products
ORDER BY discount DESC NULLS LAST;

13. Using ORDER BY with LIMIT

You can combine ORDER BY with LIMIT to retrieve top/bottom results:

sqlCopyEdit-- Top 5 highest salaries
SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 5;

14. Combining ORDER BY with WHERE

sqlCopyEditSELECT name, age FROM users
WHERE is_active = TRUE
ORDER BY age DESC;

Filter first, then sort — always.

15. Real-World Examples of Sorting

Sort Top 3 Cities by Number of Users:

sqlCopyEditSELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
ORDER BY user_count DESC
LIMIT 3;

Sort Products by Price (Low to High):

sqlCopyEditSELECT product_name, price FROM products
ORDER BY price ASC;

16. Performance Considerations

• Sorting large datasets may slow down performance.
• Use indexes on frequently sorted columns.
• Avoid sorting unfiltered data unless necessary.

17. ORDER BY vs GROUP BY

18. Common Mistakes and How to Avoid Them

• Assuming implicit order: SQL results are not ordered unless you specify ORDER BY
• Sorting by columns not included in SELECT without understanding scope
• Using column positions instead of names — prone to break in refactors
• Sorting before filtering (should be after WHERE)

19. Best Practices for Sorting

• Always explicitly specify ASC or DESC for clarity
• Use column names over column positions
• Combine ORDER BY with LIMIT for better performance on top results
• Add indexes to columns that are frequently sorted

20. Summary and What’s Next

The ORDER BY clause empowers you to return data in a meaningful order, making your results more readable and more useful in applications. By sorting on columns like names, prices, dates, or computed values, you can build everything from reports to dashboards.