ORDER BY - Sorting Results

Sort query results in ascending or descending order.

14 min read
Beginner

Introduction to ORDER BY

You've learned to SELECT data and filter it with WHERE. But have you noticed the results come back in no particular order? Sometimes you get employees sorted by ID, sometimes randomly by when they were inserted.

What if you want to see products from cheapest to most expensive? Or employees sorted alphabetically? Or sales data from newest to oldest?

That's where ORDER BY comes in! It lets you sort your query results in ascending or descending order.

A SQL clause that sorts query results by one or more columns. You can sort in ascending order (smallest to largest, A to Z) or descending order (largest to smallest, Z to A).

Basic Syntax:

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

Let's work with our products table:

products
id
product_name
category
price
stock
rating
1LaptopElectronics999.99454.5
2MouseElectronics29.991204.2
3Desk ChairFurniture199.99304.7
4MonitorElectronics349.99604.6
5Desk LampFurniture39.99854.3
6KeyboardElectronics79.99954.4

Sorting in Ascending Order (ASC)

By default, ORDER BY sorts in ascending order (ASC). This means:

  • Numbers: smallest to largest (1, 2, 3...)
  • Text: alphabetically (A, B, C...)
  • Dates: oldest to newest

ASC is optional - if you don't specify ASC or DESC, SQL assumes ASC.

sql
-- Sort products by price (cheapest first)
SELECT product_name, price
FROM products
ORDER BY price ASC;

Products are now sorted from cheapest to most expensive!

You can omit ASC since it's the default:

sql
-- Same result - ASC is implied
SELECT product_name, price
FROM products
ORDER BY price;

Sorting text alphabetically:

sql
-- Sort products by name (A to Z)
SELECT product_name, category
FROM products
ORDER BY product_name;

Perfect alphabetical order from A to Z!

Sorting in Descending Order (DESC)

To reverse the order, use DESC (descending). This gives you:

  • Numbers: largest to smallest (10, 9, 8...)
  • Text: reverse alphabetically (Z, Y, X...)
  • Dates: newest to oldest
sql
-- Sort products by price (most expensive first)
SELECT product_name, price
FROM products
ORDER BY price DESC;

Now we see the most expensive products first!

Sorting by rating (highest rated first):

sql
-- Best rated products first
SELECT product_name, rating
FROM products
ORDER BY rating DESC;

Perfect for showing top-rated products to customers!

Sorting by Multiple Columns

You can sort by multiple columns by listing them separated by commas. SQL sorts by the first column, then uses the second column as a "tiebreaker" when the first column has duplicate values.

Syntax:

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
sql
-- Sort by category first, then by price within each category
SELECT product_name, category, price
FROM products
ORDER BY category ASC, price ASC;

Notice how:

  1. Products are first grouped by category (Electronics, then Furniture)
  2. Within each category, products are sorted by price (cheapest to most expensive)

You can mix ASC and DESC:

sql
-- Categories A-Z, but highest price first within each category
SELECT product_name, category, price
FROM products
ORDER BY category ASC, price DESC;

Now we see categories alphabetically, but the most expensive products in each category appear first!

ORDER BY with WHERE Clause

You can combine ORDER BY with WHERE to filter AND sort results. Remember the order:

  1. FROM - which table
  2. WHERE - filter rows
  3. ORDER BY - sort results

Query Order: WHERE always comes BEFORE ORDER BY! This is the correct order:

SELECT ... FROM ... WHERE ... ORDER BY ...
sql
-- Find Electronics products, sorted by price
SELECT product_name, price, stock
FROM products
WHERE category = 'Electronics'
ORDER BY price DESC;

Only Electronics products are shown (WHERE filters), sorted from most to least expensive (ORDER BY sorts).

Another example with multiple conditions:

sql
-- Low-stock items (less than 50), sorted by stock level
SELECT product_name, stock, price
FROM products
WHERE stock < 50
ORDER BY stock ASC;

Shows only products with stock < 50, with the lowest stock items first. Perfect for identifying what needs to be reordered urgently!

Sorting by Column Position

Instead of column names, you can use column numbers based on their position in the SELECT list. While this works, it's generally not recommended because it makes queries harder to read.

sql
-- Sort by the 2nd column (price)
SELECT product_name, price, stock
FROM products
ORDER BY 2 DESC;

Best Practice: Use column names instead of numbers (ORDER BY price instead of ORDER BY 2). Column names are clearer and won't break if you rearrange columns in your SELECT list.

Sorting with NULL Values

How does ORDER BY handle NULL values? It depends on the database, but most systems treat NULL as either the smallest or largest value.

products_with_discount
product_name
price
discount
Laptop999.99NULL
Mouse29.995.00
Monitor349.99NULL
Keyboard79.9910.00
sql
-- Sort by discount
SELECT product_name, discount
FROM products_with_discount
ORDER BY discount ASC;

In most databases (MySQL, PostgreSQL), NULL appears first with ASC and last with DESC.

With DESC:

sql
SELECT product_name, discount
FROM products_with_discount
ORDER BY discount DESC;

Database differences: Some databases (like SQL Server) put NULL last with ASC. If NULL ordering matters for your application, check your database documentation or use NULLS FIRST / NULLS LAST (if supported).

Practical Examples

Example 1: E-commerce product listing

sql
-- Show in-stock products, best rated first
SELECT product_name, price, rating, stock
FROM products
WHERE stock > 0
ORDER BY rating DESC, price ASC;

Perfect for showing customers the best-rated products first, with cheaper options prioritized when ratings are tied!

Example 2: Employee directory

employees
id
last_name
first_name
department
hire_date
1SmithAliceEngineering2020-03-15
2JohnsonBobMarketing2021-07-22
3SmithCharlieEngineering2019-11-03
4DavisDianaSales2022-01-10
sql
-- Sort employees alphabetically (last name, then first name)
SELECT last_name, first_name, department
FROM employees
ORDER BY last_name ASC, first_name ASC;

Notice how the two Smiths are sorted by first name (Alice before Charlie).

Example 3: Recent activity report

sql
-- Show newest employees first
SELECT first_name, last_name, hire_date, department
FROM employees
ORDER BY hire_date DESC;

Most recently hired employees appear first.

Common Mistakes to Avoid

Don't make these mistakes:

  1. Putting ORDER BY before WHERE: ❌

    • Wrong: SELECT * FROM products ORDER BY price WHERE stock > 0;
    • Right: SELECT * FROM products WHERE stock > 0 ORDER BY price;

  2. Forgetting column exists in table: ❌

    • ORDER BY rating fails if there's no rating column
    • Always verify column names

  3. Assuming default is DESC: ❌

    • Default is ASC (ascending), not DESC
    • If you want descending, you MUST specify DESC

  4. Using quotes around column names unnecessarily: ❌

    • ORDER BY 'price' sorts by the literal string 'price', not the column!
    • Use: ORDER BY price (no quotes)

  5. Sorting by columns not in SELECT: This works but can be confusing

    • SELECT product_name FROM products ORDER BY price;
    • Works, but readers can't see what it's sorted by

  6. Using column positions instead of names: ❌ (works but hard to maintain)

    • ORDER BY 2, 3 - what are columns 2 and 3?
    • Better: ORDER BY category, price

Key Takeaways

What you learned:

✅ ORDER BY sorts query results by one or more columns ✅ ASC sorts ascending (default) - smallest to largest, A to Z ✅ DESC sorts descending - largest to smallest, Z to A ✅ You can sort by multiple columns - first column, then tiebreakers ✅ WHERE comes before ORDER BY in query syntax ✅ NULL values typically appear first with ASC, last with DESC ✅ ORDER BY works with numbers, text, and dates ✅ Always use column names instead of positions for clarity

Coming up next: We'll learn about LIMIT and OFFSET to control how many rows are returned and implement pagination!

Practice Exercise: Try these queries:

  1. Sort products by stock (lowest stock first)
  2. Find Electronics products sorted by price DESC
  3. Sort by category ASC, then rating DESC, then price ASC
  4. Find products with price > 50, sorted by product name alphabetically
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.