How do I sort query results using ORDER BY?

By Wayan in SQL 0 Comment

When you run a SELECT statement, the database does not promise to return rows in any particular order. If you want a predictable sequence — the cheapest books first, the newest orders on top, or authors listed alphabetically — you have to ask for it explicitly. The tool SQL gives you for that is the ORDER BY clause.

In this tutorial you will learn how to sort rows in ascending or descending order, sort by more than one column, sort by expressions and aliases, and control where NULL values appear. You will also see the most common mistakes and how each dialect handles the differences.

Prerequisites

To follow along, you need:

  • A working SQL database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite).
  • A client where you can execute SQL statements (a GUI tool like DBeaver, or a command-line client).
  • Basic familiarity with the SELECT and WHERE clauses.

If you have not created a sample database yet, the setup script in the next section will produce everything you need.

Sample Database

We continue with the small online bookstore schema used in the previous tutorial. It contains two tables: authors and books.

CREATE TABLE authors (
    author_id   INTEGER      PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    country     VARCHAR(50)
);

CREATE TABLE books (
    book_id      INTEGER       PRIMARY KEY,
    title        VARCHAR(200)  NOT NULL,
    author_id    INTEGER       NOT NULL,
    category     VARCHAR(50),
    price        DECIMAL(8, 2) NOT NULL,
    stock        INTEGER       NOT NULL,
    published_on DATE,
    CONSTRAINT fk_books_author
        FOREIGN KEY (author_id) REFERENCES authors (author_id)
);

INSERT INTO authors (author_id, author_name, country) VALUES
    (1, 'Jane Austen',              'United Kingdom'),
    (2, 'Haruki Murakami',          'Japan'),
    (3, 'Chimamanda Ngozi Adichie', 'Nigeria'),
    (4, 'Gabriel Garcia Marquez',   'Colombia');

INSERT INTO books (book_id, title, author_id, category, price, stock, published_on) VALUES
    (1, 'Pride and Prejudice',           1, 'Classic',      12.50, 20, '1813-01-28'),
    (2, 'Emma',                          1, 'Classic',      10.00,  0, '1815-12-23'),
    (3, 'Norwegian Wood',                2, 'Fiction',      15.75, 12, '1987-09-04'),
    (4, 'Kafka on the Shore',            2, 'Fiction',      18.20,  5, '2002-09-12'),
    (5, 'Half of a Yellow Sun',          3, 'Historical',   16.00,  8, '2006-08-11'),
    (6, 'Americanah',                    3, 'Contemporary', 14.50,  3, '2013-05-14'),
    (7, 'One Hundred Years of Solitude', 4, 'Classic',      22.00, 25, '1967-05-30'),
    (8, 'Love in the Time of Cholera',   4, 'Classic',      19.99,  0, '1985-09-05'),
    (9, 'Unknown Title',                 2, NULL,           13.00,  4, NULL);

The last row contains NULL values for category and published_on. We will use it to see how ORDER BY treats missing data.

Basic Syntax

The ORDER BY clause appears after WHERE (and after GROUP BY / HAVING when they are present), and before LIMIT.

SELECT column_list
FROM table_name
WHERE condition
ORDER BY sort_expression [ASC | DESC] [, sort_expression [ASC | DESC] ...];
  • sort_expression is usually a column name, but it can also be an expression, an alias, or a column position number.
  • ASC sorts from smallest to largest and is the default.
  • DESC sorts from largest to smallest.
  • Without ORDER BY, the returned row order is undefined, even if it looks stable during testing.

Practical Example

The bookstore manager wants a price list of every book, from the cheapest to the most expensive.

SELECT
    book_id,
    title,
    price
FROM books
ORDER BY price ASC;

Reading the query in logical order:

  1. FROM books — start with every row in the books table.
  2. SELECT book_id, title, price — pick the three columns to return.
  3. ORDER BY price ASC — sort the result set by price from lowest to highest.

Expected Result

book_id title price
2 Emma 10.00
1 Pride and Prejudice 12.50
9 Unknown Title 13.00
6 Americanah 14.50
3 Norwegian Wood 15.75
5 Half of a Yellow Sun 16.00
4 Kafka on the Shore 18.20
8 Love in the Time of Cholera 19.99
7 One Hundred Years of Solitude 22.00

Additional Examples

1. Descending Order

Use DESC when the largest value should come first, for example a “most expensive first” list.

SELECT
    title,
    price
FROM books
ORDER BY price DESC;

2. Sorting by Multiple Columns

You can sort by more than one column. Each column can independently use ASC or DESC. The database sorts by the first expression, then breaks ties with the second, and so on.

SELECT
    category,
    title,
    price
FROM books
WHERE category IS NOT NULL
ORDER BY
    category ASC,
    price    DESC;

“Group books by category alphabetically, and within each category show the most expensive book first.”

Expected result:

category title price
Classic One Hundred Years of Solitude 22.00
Classic Love in the Time of Cholera 19.99
Classic Pride and Prejudice 12.50
Classic Emma 10.00
Contemporary Americanah 14.50
Fiction Kafka on the Shore 18.20
Fiction Norwegian Wood 15.75
Historical Half of a Yellow Sun 16.00

3. Sorting by an Expression

ORDER BY accepts any expression that the database can evaluate — arithmetic, string operations, function calls, and so on.

SELECT
    title,
    price,
    stock,
    price * stock AS inventory_value
FROM books
ORDER BY price * stock DESC;

The rows are ordered by the computed inventory value even though the raw price and stock columns are not sorted individually.

4. Sorting by an Alias

Most databases allow you to use the alias defined in the SELECT list, because ORDER BY is logically evaluated after SELECT.

SELECT
    title,
    price * stock AS inventory_value
FROM books
ORDER BY inventory_value DESC;

This is often more readable than repeating the full expression. Availability varies slightly — see the Database Compatibility section for details.

5. Sorting Strings

String columns are sorted according to the column’s collation, which controls case sensitivity and locale-aware rules.

SELECT
    author_name,
    country
FROM authors
ORDER BY author_name ASC;

Expected result:

author_name country
Chimamanda Ngozi Adichie Nigeria
Gabriel Garcia Marquez Colombia
Haruki Murakami Japan
Jane Austen United Kingdom

If your data mixes upper- and lower-case letters and the ordering surprises you, check the collation for your column or database.

6. Sorting Dates

Dates and timestamps sort chronologically. ASC produces oldest-first; DESC produces newest-first.

SELECT
    title,
    published_on
FROM books
WHERE published_on IS NOT NULL
ORDER BY published_on DESC;

7. Controlling NULL Placement

NULL values need special treatment because they are neither less than nor greater than any real value. Different databases place them in different positions by default:

  • PostgreSQL and Oracle Database place NULL values last in ASC order and first in DESC order.
  • MySQL, MariaDB, and SQLite place NULL values first in ASC order and last in DESC order.
  • SQL Server places NULL values first in ASC order and last in DESC order.

ANSI SQL defines the optional NULLS FIRST and NULLS LAST modifiers to make the placement explicit.

-- PostgreSQL, Oracle Database, SQLite 3.30+
SELECT
    title,
    published_on
FROM books
ORDER BY published_on ASC NULLS LAST;

If your database does not support NULLS FIRST / NULLS LAST, you can emulate them with a helper expression:

-- Portable emulation: put NULLs last in ascending order
SELECT
    title,
    published_on
FROM books
ORDER BY
    CASE WHEN published_on IS NULL THEN 1 ELSE 0 END,
    published_on ASC;

8. Combining ORDER BY with LIMIT

ORDER BY is what makes LIMIT meaningful. Asking for the “top 3” without sorting first is not really a top-3 query.

-- PostgreSQL, MySQL, MariaDB, SQLite
SELECT
    title,
    price
FROM books
ORDER BY price DESC
LIMIT 3;

Equivalent examples for other systems:

-- SQL Server
SELECT TOP 3
    title,
    price
FROM books
ORDER BY price DESC;

-- Oracle Database and ANSI SQL
SELECT
    title,
    price
FROM books
ORDER BY price DESC
FETCH FIRST 3 ROWS ONLY;

9. Using ORDER BY in Application Code

When your application performs paging or user-selected sorting, keep the sort expression on the server side and pass only the values you actually want to filter by as parameters.

String sql = """
        SELECT book_id, title, price
        FROM books
        WHERE category = ?
        ORDER BY price DESC
        """;

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, category);

    try (ResultSet resultSet = statement.executeQuery()) {
        while (resultSet.next()) {
            long bookId = resultSet.getLong("book_id");
            String title = resultSet.getString("title");
            BigDecimal price = resultSet.getBigDecimal("price");
            // process the row...
        }
    }
}

Never build the ORDER BY clause by concatenating user input directly into the SQL string. If users may choose the sort column, validate the incoming value against a fixed allow-list of column names before assembling the query.

Common Mistakes

Assuming a Query Is Sorted Without ORDER BY

Incorrect:

SELECT title, price
FROM books;
-- "The rows come back sorted by book_id anyway."

The order you happen to observe is an artifact of the current storage layout, statistics, and execution plan. It can change silently after an update, an index change, or an upgrade. If order matters, always request it:

SELECT title, price
FROM books
ORDER BY book_id;

Sorting by Column Position Numbers

Some databases allow ordering by the position of a column in the SELECT list, for example ORDER BY 2. This is fragile — inserting a new column silently changes the sort. Prefer explicit column names or aliases.

Fragile:

SELECT title, price
FROM books
ORDER BY 2 DESC;

Better:

SELECT title, price
FROM books
ORDER BY price DESC;

Forgetting a Tie-Breaker for Deterministic Ordering

If several rows share the same value in the sort column, their relative order is undefined. For paging or reproducible reports, add a unique tie-breaker such as the primary key.

SELECT
    title,
    price
FROM books
ORDER BY
    price   DESC,
    book_id ASC;

Ignoring NULL Placement Differences

If your query is portable across databases, do not rely on the default position of NULL values. Use NULLS FIRST / NULLS LAST where supported, or the CASE-based emulation shown earlier.

Database Compatibility

The core ORDER BY syntax with ASC and DESC is part of ANSI SQL and is supported by:

  • PostgreSQL
  • MySQL
  • MariaDB
  • SQL Server
  • Oracle Database
  • SQLite

Notable differences you may encounter:

  • NULLS FIRST / NULLS LAST. Supported by PostgreSQL, Oracle Database, and SQLite 3.30+. MySQL, MariaDB, and SQL Server do not accept these keywords; use a CASE expression to control placement.
  • Default NULL position. As described in section 7, defaults differ across engines. When in doubt, be explicit.
  • Row limiting. Combine ORDER BY with LIMIT (PostgreSQL, MySQL, MariaDB, SQLite), TOP (SQL Server), or FETCH FIRST n ROWS ONLY (Oracle Database and ANSI SQL).
  • Sorting by alias. PostgreSQL, MySQL, MariaDB, SQLite, and SQL Server accept SELECT-list aliases in ORDER BY. Oracle Database also accepts them in most cases, but repeating the expression is always safe.
  • Collation and case sensitivity. String ordering depends on the column’s collation. Two databases with different default collations can order the same data differently.

When in doubt, consult the documentation for the specific database and version you are using.

Best Practices

  • Always sort explicitly when order matters. The database is free to return rows in any order otherwise.
  • Add a deterministic tie-breaker. A unique column such as the primary key guarantees stable ordering, especially with LIMIT or paging.
  • Prefer column names over positions. Ordering by 1, 2, 3 breaks silently when the SELECT list changes.
  • Be explicit about NULL placement when writing portable SQL.
  • Sort on indexed columns when possible. An index on the sort column can let the database skip an expensive sort step. Measure with your database’s execution-plan tool before assuming a benefit.
  • Sort in the database, not in the application. The database can use indexes and streaming; loading everything into memory to sort in Java is slower and wastes bandwidth.
  • Validate user-supplied sort keys against an allow-list. Never inject raw user input into an ORDER BY clause.

Conclusion

You learned how to use the SQL ORDER BY clause to sort query results in ascending or descending order, sort by multiple columns and expressions, and control how NULL values are positioned. You also saw why a deterministic tie-breaker matters for paging and how dialects differ in NULL handling and row limiting.