SQL ORDER BY – Syntax, ASC/DESC, Multiple Columns, and Pagination | DbSchema

Table of Contents

1. What SQL ORDER BY does
2. Syntax
3. Sort ascending and descending
4. Sort by multiple columns
5. Sort by column position
6. Sort by expressions and functions
7. ORDER BY with NULL values
8. ORDER BY with WHERE and GROUP BY
9. ORDER BY with LIMIT, TOP, and FETCH
10. Case-insensitive and custom sorting
11. ORDER BY in subqueries and CTEs
12. Common mistakes and performance tips
13. Use ORDER BY in DbSchema
14. FAQ
15. Conclusion

The SQL ORDER BY clause sorts the rows returned by a SELECT query in ascending (ASC) or descending (DESC) order. Without it, the database returns rows in an undefined order that can change between executions.

ORDER BY is one of the most-used clauses in SQL. It appears in reports, dashboards, paginated APIs, leaderboards, and any query where the user expects a meaningful sequence. In DbSchema, it is also one of the fastest ways to inspect live data because you can sort, rerun, and compare result sets while keeping the schema visible.

What SQL ORDER BY does

ORDER BY is placed at the end of a SELECT statement and controls the sort order of the result set.

• ASC — smallest to largest, A to Z, oldest to newest (default)
• DESC — largest to smallest, Z to A, newest to oldest

Without ORDER BY, row order is not guaranteed by the SQL standard.

Syntax

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

ORDER BY is always the last clause in a SELECT statement, except for database-specific row-limiting keywords such as LIMIT, OFFSET, FETCH FIRST, or TOP.

If you want a refresher on the full query layout, see SQL SELECT and SQL WHERE Clause.

Sort ascending and descending

Sample table used throughout this guide:

CREATE TABLE Employees (
    employee_id INT,
    name        VARCHAR(50),
    salary      INT,
    department  VARCHAR(50)
);

INSERT INTO Employees VALUES
    (1, 'John',  3000, 'Marketing'),
    (2, 'Alex',  5000, 'Sales'),
    (3, 'Sara',  4000, 'HR'),
    (4, 'Maria', 6000, 'Sales'),
    (5, 'Tom',   5000, 'Marketing');

Ascending (default)

SELECT * FROM Employees ORDER BY salary ASC;

Descending

SELECT * FROM Employees ORDER BY salary DESC;

Sort by multiple columns

When two rows tie on the first sort column, the database uses the next column to break the tie.

SELECT * FROM Employees
ORDER BY department ASC, salary DESC;

Departments are sorted A→Z, and within each department employees are sorted by salary high→low.

Sort by column position

You can reference a column by its position in the SELECT list instead of its name:

SELECT name, salary, department
FROM Employees
ORDER BY 2 DESC; -- sorts by salary (column 2)

This is convenient in quick queries but can be fragile if column order changes, so avoid it in production code.

Sort by expressions and functions

ORDER BY accepts any valid expression, not just bare column names.

Sort by string length

SELECT name FROM Employees ORDER BY LENGTH(name) ASC;

Sort by computed value

SELECT name, salary,
       salary * 12 AS annual_salary
FROM Employees
ORDER BY annual_salary DESC;

Most databases allow ORDER BY to reference an alias defined in SELECT.

Sort by CASE expression

SELECT name, department
FROM Employees
ORDER BY
    CASE department
        WHEN 'Sales'     THEN 1
        WHEN 'Marketing' THEN 2
        ELSE                  3
    END;

This is useful when you need a custom sort order that alphabetical ordering cannot express.

ORDER BY with NULL values

Different databases handle NULLs in sort order differently:

PostgreSQL / Oracle: NULLS FIRST / NULLS LAST

SELECT name, salary
FROM Employees
ORDER BY salary ASC NULLS LAST;

MySQL / SQL Server workaround

-- Push NULLs to the bottom in MySQL
SELECT name, salary
FROM Employees
ORDER BY salary IS NULL, salary ASC;

ORDER BY with WHERE and GROUP BY

ORDER BY is applied after WHERE filters rows and after GROUP BY aggregates them:

SELECT department,
       COUNT(*)       AS employee_count,
       AVG(salary)    AS average_salary
FROM Employees
WHERE salary > 3000
GROUP BY department
ORDER BY average_salary DESC;

For a deeper look at aggregating before sorting, see SQL COUNT, AVG, and SUM Functions, SQL HAVING Clause, and SQL GROUP BY Explained.

ORDER BY with LIMIT, TOP, and FETCH

Sorting becomes even more important when you return only part of a result set.

MySQL / PostgreSQL: ORDER BY with LIMIT

SELECT employee_id, name, salary
FROM Employees
ORDER BY salary DESC, employee_id ASC
LIMIT 3;

SQL Server: ORDER BY with TOP

SELECT TOP 3 employee_id, name, salary
FROM Employees
ORDER BY salary DESC, employee_id ASC;

Standard SQL / Oracle / PostgreSQL: FETCH FIRST

SELECT employee_id, name, salary
FROM Employees
ORDER BY salary DESC, employee_id ASC
FETCH FIRST 3 ROWS ONLY;

The extra employee_id ASC tie-breaker matters. Without a deterministic secondary sort, page 1 and page 2 can return overlapping or missing rows when values tie.

Case-insensitive and custom sorting

Competitor pages often stop at ASC and DESC, but real applications usually need custom text sorting too.

Case-insensitive alphabetical sorting

SELECT name
FROM Employees
ORDER BY LOWER(name) ASC;

This makes Alex and alex sort consistently even when the database collation is case-sensitive.

Custom business order with CASE

SELECT name, department
FROM Employees
ORDER BY
    CASE department
        WHEN 'Executive' THEN 1
        WHEN 'Sales' THEN 2
        WHEN 'Marketing' THEN 3
        ELSE 4
    END,
    name ASC;

Use this pattern when alphabetical order is not the same as business priority.

ORDER BY in subqueries and CTEs

In most databases, ORDER BY inside a subquery or CTE is either ignored or disallowed unless paired with LIMIT / TOP. Always sort in the outermost query.

-- Correct: sort in the outer query
WITH top_earners AS (
    SELECT name, salary
    FROM Employees
    WHERE salary > 4000
)
SELECT * FROM top_earners
ORDER BY salary DESC;

Common mistakes and performance tips

Common mistakes

1. Relying on implicit ordering — Never assume rows come back in a consistent order without ORDER BY.
2. Sorting inside a subquery — The sort is usually ignored; sort in the outermost SELECT.
3. Returning unstable pages — When using LIMIT / OFFSET, add a unique tie-breaker such as the primary key.
4. Sorting on expressions unnecessarily — ORDER BY LOWER(name) may prevent index use unless you have a matching functional index.
5. Forgetting NULL behavior varies by database — Review SQL NULL Values when a sort looks wrong.

Performance tip

Large sorts can be expensive if the database has to build a temporary sort result. When a query sorts often on the same column, an index can help:

CREATE INDEX idx_employees_salary ON Employees (salary DESC);

After that, inspect the plan before and after the change. If you want a lightweight example of plan analysis, see EXPLAIN QUERY PLAN in SQLite.

Use ORDER BY in DbSchema

DbSchema is a visual database client that makes it easy to build and test sorted queries without writing SQL from scratch.

1. Connect to your database using the PostgreSQL JDBC driver, MySQL JDBC driver, or any other supported driver.
2. Open the SQL Editor and write your ORDER BY query.
3. Run the query and inspect the sorted result set in the table view.
4. Switch to the Query Builder to add sort columns visually via drag-and-drop.
5. Compare the sorted data with the table structure in the schema documentation when you need to explain query behavior to teammates.

This is particularly useful when exploring unfamiliar schemas, building reports, or verifying that pagination queries return rows in a predictable order.

FAQ

What is the default sort order in SQL ORDER BY?

Ascending (ASC). Rows are sorted from smallest to largest, A to Z, or oldest to newest unless you specify DESC.

Can I order by a column that is not in the SELECT list?

Yes, you can. The column does not need to appear in the SELECT list to be used in ORDER BY.

Can I use ORDER BY with multiple columns?

Yes. List them separated by commas. The database sorts by the first column, then uses subsequent columns to break ties.

How does ORDER BY handle NULL values?

It depends on the database. PostgreSQL and Oracle put NULLs last in ascending order; MySQL and SQL Server put them first. Use NULLS FIRST / NULLS LAST (where supported) to make the behavior explicit.

What is the difference between ORDER BY and GROUP BY?

ORDER BY sorts the final result set. GROUP BY combines rows into summary groups before you calculate aggregates such as COUNT() or SUM().

Does ORDER BY slow down a query?

Sorting large result sets without an index can be expensive. An index on the sort column can eliminate the need for an explicit sort step. Always check the execution plan when performance matters.

How do I paginate results safely with ORDER BY?

Sort on a stable set of columns, ideally including a unique tie-breaker such as employee_id. That prevents rows from moving between pages when two rows have the same sort value.

Can I use ORDER BY inside a subquery?

In most databases, ORDER BY inside a subquery without LIMIT/TOP is either ignored or causes an error. Sort in the outer query.

Conclusion

The SQL ORDER BY clause controls the sort order of query results. Use ASC for ascending and DESC for descending, combine multiple columns for multi-level sorting, and add deterministic tie-breakers when you paginate or export results.

Connect DbSchema to your database to build and test sorted queries visually, compare query output to the schema, and validate paginated report output before it reaches production.