SQL ORDER BY to Sort Query Results

Every query you have written so far returns rows in whatever order the database happens to store them. Sometimes the results appear in the order you inserted the data. Sometimes they do not. The truth is, without an explicit sorting instruction, SQL makes no guarantees about row order. The same query can return rows in a different sequence today than it did yesterday.

This is where the ORDER BY clause comes in. It gives you complete control over how your results are arranged: alphabetically by name, from cheapest to most expensive, from newest to oldest, or by any combination of columns you need. It is one of the most frequently used clauses in SQL, and understanding it fully will make every report, every listing page, and every data export you produce look exactly the way you want.

This guide covers everything you need to know about ORDER BY: ascending and descending sort directions, sorting by multiple columns, using column positions as shortcuts, and the often-overlooked behavior of NULL values in sorted results. Every example uses the ShopSmart sample database with full outputs so you can follow along in your own environment (we defined it in a previous guide here).

How ORDER BY Works

The ORDER BY clause is added after the WHERE clause (or after FROM if there is no WHERE) and tells the database how to arrange the rows in your results.

SELECT columns
FROM table
WHERE condition      -- optional
ORDER BY column;

Without ORDER BY, the database returns rows in an undefined order. It might look consistent during development, but it is not guaranteed. Adding ORDER BY gives you a predictable, reliable sequence every time.

Here is a quick demonstration. First, without any sorting:

SELECT name, price
FROM products;

Output (order is not guaranteed):

Now, with ORDER BY:

SELECT name, price
FROM products
ORDER BY price;

Output (sorted by price, lowest first):

The rows are now arranged in a clear, predictable sequence from cheapest to most expensive. No matter how many times you run this query, the order will always be the same.

Ascending Order (ASC)

Ascending order arranges values from smallest to largest (for numbers), from A to Z (for text), and from earliest to latest (for dates). It is the default sort direction in SQL.

-- These two queries produce identical results
SELECT name, price
FROM products
ORDER BY price ASC;

SELECT name, price
FROM products
ORDER BY price;

Since ASC is the default, you do not need to type it. Both queries sort products from lowest to highest price. However, many developers include ASC explicitly for clarity, especially when the query also uses DESC on other columns.

Ascending Sort on Text

When sorting text columns in ascending order, rows are arranged alphabetically (A to Z):

SELECT name, price
FROM products
ORDER BY name ASC;

Output:

Products are listed in alphabetical order by name. Note that uppercase and lowercase letters, special characters, and numbers may affect the sort order depending on your database's collation settings, but for standard English text the result is predictable alphabetical sorting.

Ascending Sort on Dates

When sorting dates in ascending order, rows are arranged from earliest to latest (oldest first):

SELECT first_name, last_name, signup_date
FROM customers
ORDER BY signup_date ASC;

Output:

The customers who signed up first appear at the top.

Descending Order (DESC)

Descending order is the reverse of ascending: largest to smallest (numbers), Z to A (text), and latest to earliest (dates). You activate it by adding the DESC keyword after the column name.

SELECT name, price
FROM products
ORDER BY price DESC;

Output:

The most expensive product appears first, and the cheapest appears last.

Descending Sort on Dates

Descending dates show the most recent first, which is the most common sort order for things like order histories, activity logs, and news feeds:

SELECT id, order_date, total_amount, status
FROM orders
ORDER BY order_date DESC;

Output:

The newest orders appear at the top. This is exactly how an order management dashboard would display recent activity.

Descending Sort on Text

Descending text sorting arranges values from Z to A:

SELECT first_name, last_name
FROM customers
ORDER BY last_name DESC;

Output:

Sorting by Multiple Columns

Single-column sorting works well until two or more rows have the same value in the sorted column. What order should those tied rows appear in? Without a secondary sort, the database decides arbitrarily.

Sorting by multiple columns solves this by defining a tiebreaker hierarchy. When rows share the same value in the first sort column, the second sort column determines their order. If they also tie on the second column, the third column breaks that tie, and so on.

SELECT columns
FROM table
ORDER BY column1, column2, column3;

A Practical Example

Let us sort customers by city. Multiple customers live in the same city, so we need a tiebreaker:

-- Sort by city only: Tied rows have unpredictable order
SELECT first_name, last_name, city
FROM customers
ORDER BY city;

Output:

Chicago and New York each have two customers. Within those groups, the order is undefined. Let us add last_name as a secondary sort:

-- Sort by city, then by last name within each city
SELECT first_name, last_name, city
FROM customers
ORDER BY city, last_name;

Output:

Now within New York, Chen comes before Johnson (alphabetical). Within Chicago, Singh comes before Wilson. Every row has a deterministic position.

Mixing ASC and DESC

Each column in an ORDER BY clause can have its own sort direction. This is extremely useful for real-world sorting requirements:

-- Sort by status alphabetically, then by total amount (highest first) within each status
SELECT id, order_date, total_amount, status
FROM orders
ORDER BY status ASC, total_amount DESC;

Output:

The statuses are sorted alphabetically (completed, pending, shipped). Within each status group, orders are sorted by total amount from highest to lowest.

Three-Column Sort Example

-- Sort products by availability (available first), then category, then price (cheapest first)
SELECT name, category_id, price, is_available
FROM products
ORDER BY is_available DESC, category_id ASC, price ASC;

Output:

Available products appear first (is_available DESC puts true before false). Within available products, they are grouped by category (ascending). Within each category, they are sorted by price (cheapest first). The unavailable Bluetooth Speaker sinks to the bottom.

Sorting by Column Position (Ordinal)

Instead of writing column names in the ORDER BY clause, you can use numbers that refer to the position of columns in your SELECT list. The first column is 1, the second is 2, and so on.

-- Sort by the second column in the SELECT list (price)
SELECT name, price
FROM products
ORDER BY 2;

Output:

ORDER BY 2 sorts by price because price is the second column in the SELECT list.

Multiple Ordinal Positions

You can combine ordinal positions just like column names, and mix directions:

-- Sort by category_id (3rd column, ascending), then price (2nd column, descending)
SELECT name, price, category_id
FROM products
ORDER BY 3 ASC, 2 DESC;

Output:

When Ordinal Sorting Is Useful

Ordinal sorting is convenient in a few specific situations:

• When sorting by a calculated expression that does not have a simple column name
• During quick, ad-hoc queries in an interactive session
• When the column name is very long and you want to avoid retyping it

-- Sorting by a calculated column using ordinal position
SELECT name,
       price,
       stock_quantity,
       price * stock_quantity AS total_value
FROM products
ORDER BY 4 DESC;

Output:

Here, ORDER BY 4 DESC sorts by the total_value calculated column, which is much more convenient than repeating the expression price * stock_quantity in the ORDER BY.

Combining ORDER BY with WHERE

ORDER BY and WHERE work together seamlessly. The WHERE clause filters the rows first, and then ORDER BY sorts whatever rows remain.

SELECT name, price, stock_quantity
FROM products
WHERE is_available = true
  AND price < 50
ORDER BY price ASC;

Output:

The database first filtered out unavailable products and those priced $50 or more. Then it sorted the remaining rows by price from cheapest to most expensive.

-- Most recent pending orders first
SELECT id, order_date, total_amount
FROM orders
WHERE status = 'pending'
ORDER BY order_date DESC;

Output:

NULL Behavior in Sorting

When a column contains NULL values, you need to understand where those rows end up in sorted results. Unfortunately, different databases handle this differently.

Let us first add a customer with a NULL city to demonstrate (if you have not already from the previous guide):

-- Add a customer with no city (if not already present)
INSERT INTO customers VALUES
(7, 'Grace', 'Taylor', 'grace@email.com', NULL, '2024-04-01');

Now let us sort customers by city:

SELECT first_name, last_name, city
FROM customers
ORDER BY city ASC;

Default NULL Positioning by Database

PostgreSQL output (NULLs last in ASC):

MySQL/SQLite/SQL Server output (NULLs first in ASC):

This inconsistency can cause unexpected behavior if you switch databases or if your application assumes a specific NULL position.

Controlling NULL Position with NULLS FIRST / NULLS LAST

PostgreSQL and SQLite (version 3.30+) support explicit NULL positioning using NULLS FIRST and NULLS LAST:

-- Force NULLs to appear first (PostgreSQL, SQLite 3.30+)
SELECT first_name, last_name, city
FROM customers
ORDER BY city ASC NULLS FIRST;

Output:

-- Force NULLs to appear last (PostgreSQL, SQLite 3.30+)
SELECT first_name, last_name, city
FROM customers
ORDER BY city ASC NULLS LAST;

Output:

Controlling NULL Position in MySQL and SQL Server

MySQL and SQL Server do not support NULLS FIRST / NULLS LAST syntax. Instead, you can use a CASE expression or other workarounds to control NULL placement:

-- MySQL / SQL Server: Push NULLs to the end in ascending sort
SELECT first_name, last_name, city
FROM customers
ORDER BY
    CASE WHEN city IS NULL THEN 1 ELSE 0 END,
    city ASC;

Output:

The CASE expression assigns 0 to non-NULL rows and 1 to NULL rows. Since 0 comes before 1, all non-NULL rows appear first. Then within those groups, the city ASC sort arranges them alphabetically.

Sorting by Columns Not in the SELECT List

A detail worth knowing: you can sort by a column even if it is not in your SELECT list. The database uses the column for ordering but does not include it in the visible output.

-- Sort by stock_quantity, but don't display it
SELECT name, price
FROM products
ORDER BY stock_quantity DESC;

Output:

The results are sorted by stock_quantity (highest first), but the stock_quantity column itself is not visible. USB-C Hub has 200 units (most), and Bluetooth Speaker has 0 (least).

This is useful when the sort column is relevant for ordering but not for display. However, it can make results confusing to read since the viewer cannot see why the rows are in that particular order. Use this technique thoughtfully.

Sorting by Expressions

You can sort by calculated expressions, not just raw column values. This allows powerful dynamic sorting:

-- Sort by total inventory value (price × stock)
SELECT name,
       price,
       stock_quantity,
       price * stock_quantity AS inventory_value
FROM products
ORDER BY price * stock_quantity DESC;

Output:

You can also sort by the alias name in most databases:

-- Using the alias in ORDER BY (works in MySQL, PostgreSQL, SQLite)
SELECT name,
       price * stock_quantity AS inventory_value
FROM products
ORDER BY inventory_value DESC;

Practical Exercises

Test your sorting skills with these exercises. Write each query yourself before checking the expected output.

Exercise 1

List all products sorted alphabetically by name.

SELECT name, price
FROM products
ORDER BY name ASC;

Expected output:

Exercise 2

List all orders sorted by total amount, with the largest orders first.

SELECT id, order_date, total_amount, status
FROM orders
ORDER BY total_amount DESC;

Expected output:

Exercise 3

List available products sorted by category (ascending), then by price (descending) within each category.

SELECT name, category_id, price
FROM products
WHERE is_available = true
ORDER BY category_id ASC, price DESC;

Expected output:

Exercise 4

List customers sorted by city (ascending, with NULLs last), then by signup date (most recent first) within each city.

-- PostgreSQL / SQLite 3.30+
SELECT first_name, last_name, city, signup_date
FROM customers
ORDER BY city ASC NULLS LAST, signup_date DESC;

Expected output:

Exercise 5

Find pending orders and sort them by total amount from smallest to largest.

SELECT id, order_date, total_amount
FROM orders
WHERE status = 'pending'
ORDER BY total_amount ASC;

Expected output:

Common Errors and Pitfalls

Sorting Before Filtering

-- Wrong: ORDER BY cannot come before WHERE
SELECT name, price
FROM products
ORDER BY price
WHERE price > 50;
-- ERROR: syntax error at or near "WHERE"

-- Correct: WHERE always comes before ORDER BY
SELECT name, price
FROM products
WHERE price > 50
ORDER BY price;

Using DESC Without a Column

-- Wrong: DESC needs to follow a specific column
SELECT name, price
FROM products
ORDER BY DESC price;
-- ERROR: syntax error

-- Correct: DESC comes after the column name
SELECT name, price
FROM products
ORDER BY price DESC;

Assuming Default Order Is Reliable

-- Dangerous assumption: "The rows always come back in insertion order"
SELECT name, price
FROM products;
-- The order might change after updates, deletes, vacuuming, or index changes

-- Safe: Always use ORDER BY when order matters
SELECT name, price
FROM products
ORDER BY id;
-- Now the order is guaranteed and predictable

Key Takeaways

The ORDER BY clause gives you complete control over how query results are arranged. Here is what you should remember:

• ORDER BY sorts query results and is placed after WHERE
• ASC (ascending) is the default sort direction: smallest first, A to Z, oldest first
• DESC (descending) reverses the order: largest first, Z to A, newest first
• Multiple columns create a sort hierarchy where each column breaks ties from the previous one
• Each column in a multi-column sort can have its own direction (ASC or DESC)
• Ordinal positions (ORDER BY 2) reference the SELECT list by number, but should be avoided in production code because they are fragile and hard to read
• NULL values sort differently across databases. Use NULLS FIRST / NULLS LAST (PostgreSQL, SQLite) or CASE expressions (MySQL, SQL Server) to control their placement
• You can sort by columns not in the SELECT list and by calculated expressions
• Never rely on default row order. Without ORDER BY, row order is undefined and unpredictable
• The clause order is: SELECT → FROM → WHERE → ORDER BY

With SELECT, FROM, WHERE, logical operators, and now ORDER BY, you have the tools to write queries that retrieve exactly the data you want, filtered to your specifications, and presented in exactly the order you need. The next step is learning how to limit the number of rows returned, which combines powerfully with sorting to build features like "top 10 lists" and paginated results.