SQL ORDER BY: Syntax, ASC/DESC, Multiple Columns, and Pagination

A query returns the rows in one order today and in a different order next month, and nothing in the query asked for either. ORDER BY is what fixes it: you name a column to sort on, and ASC or DESC says which end comes first. PostgreSQL 17 states the rule behind that as bluntly as it can be put, saying that "SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order".

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

ASC is what you get when you write neither direction, which SQL Server documents in one line: "ASC is the default sort order".

Sort ascending and descending

The clause goes at the end of a SELECT, after WHERE and after GROUP BY. It is the last clause in the statement, with one exception: the row-limiting keywords, LIMIT, OFFSET, FETCH FIRST, and TOP, which are covered further down. For the shape of the rest of the query, see SQL SELECT and the SQL WHERE clause.

• ASC sorts from the smallest value up: numbers from low to high, text from A to Z, dates from oldest to newest.
• DESC reverses each of those.
• Each expression in the list carries its own direction, so ORDER BY department, salary DESC sorts the departments ascending and the salaries inside them descending.

Every example on this page runs against one table of five employees:

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',   5500, 'Marketing');

SELECT * FROM Employees ORDER BY salary ASC;

SELECT * FROM Employees ORDER BY salary DESC;

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Sort by multiple columns

A second sort expression decides the order of the rows that tie on the first:

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

Departments run from HR to Sales. Marketing and Sales each hold two employees, and salary DESC decides which of the two comes first; HR has one row, so the second expression has nothing to do there.

Sort by column position, expressions, and aliases

A sort expression does not have to be a column name. A number refers to a column by its place in the SELECT list:

SELECT name, salary, department
FROM Employees
ORDER BY 2 DESC;

The 2 is the salary column, so the rows come back in the descending salary order shown above. Reorder the SELECT list later and the query still runs, sorted by whatever is now in second place, which is why SQL Server's own guidance is to "avoid specifying integers in the ORDER BY clause as positional representations" and name the column instead.

A computed expression works as well, and so does the alias you give it, because PostgreSQL 17 accepts "the column label or number of an output column" as a sort expression:

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

The alias has to stand alone. ORDER BY annual_salary DESC sorts; ORDER BY annual_salary + 1 is rejected in PostgreSQL 17 and in SQL Server, both of which take an output-column name only as the whole expression.

A column the SELECT list leaves out is still a legal sort expression in a plain SELECT. DISTINCT and the set operators are the exception: SQL Server requires that with UNION, EXCEPT, INTERSECT, or DISTINCT, "you must define column names and aliases specified in the ORDER BY clause in the select list", and Oracle Database 19c places the same restriction on DISTINCT.

ORDER BY with WHERE and GROUP BY

The sort runs last, on whatever WHERE and GROUP BY left behind, so a grouped query can be sorted by an aggregate it computed:

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

John is filtered out by the WHERE clause before the grouping, which is why Marketing shows one employee and an average of 5500 rather than two employees and 4250. Marketing and Sales then tie on the average, and the department expression breaks the tie.

The two clauses are not alternatives. GROUP BY collapses rows into one row per group and changes how many rows come back; ORDER BY changes the order of the rows and never their number. SQL COUNT, AVG, and SUM Functions, the SQL HAVING clause, and SQL GROUP BY explained cover what happens before the sort.

ORDER BY with LIMIT, TOP, and FETCH

Returning part of a result makes the sort load-bearing, because the sort is what decides which part you get. MySQL and PostgreSQL write the limit at the end:

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

Replace LIMIT 3 with the standard-SQL spelling, FETCH FIRST 3 ROWS ONLY, and the same three rows come back. PostgreSQL 17 says that "SQL:2008 introduced a different syntax to achieve the same result, which PostgreSQL also supports", and Oracle Database 19c documents that clause as the way to "implement top-N reporting".

SQL Server puts the count in the select list instead, and its documentation asks for the sort every time: "In a SELECT TOP (n) statement, always use an ORDER BY clause. This is the only way to predictably indicate which rows TOP affects".

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

Paging through a result uses OFFSET to skip what the previous page returned. SQL Server spells that pair OFFSET ... ROWS FETCH NEXT ... ROWS ONLY, where FIRST and NEXT, and ROW and ROWS, are "synonyms and are provided for ANSI compatibility":

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

The tie-breaker on employee_id changes none of the results above, because every salary in this table is distinct. It earns its place the day two employees are paid the same. Without it, those two rows can both land on page 1 in one run and straddle the page boundary in the next, so a row is shown twice and another is never shown at all. PostgreSQL 17 warns that a LIMIT whose sort does not constrain the rows into a unique order returns "an unpredictable subset of the query's rows".

ORDER BY with NULL values

A sixth employee arrives with no salary on file:

INSERT INTO Employees VALUES (6, 'Nina', NULL, 'HR');

Where that row lands depends on the engine, and the four do not agree:

PostgreSQL 17 sorts null "as if larger than any non-null value", which is where its ascending default comes from, and Oracle Database 19c documents the same two defaults. MySQL 8.4 goes the other way, and SQL Server arrives at MySQL's answer by another route, because for it "NULL values are treated as the lowest possible values".

PostgreSQL and Oracle accept NULLS LAST and its opposite NULLS FIRST, whatever the direction of the sort:

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

The other two engines have no such clause. MySQL 8.4 takes a column, an expression, or a position, with ASC or DESC and nothing else, so you sort on the test itself first. salary IS NULL is 0 for a row with a salary and 1 for Nina, so ascending order puts the nulls at the end and returns the same six rows:

SELECT name, salary
FROM Employees
ORDER BY salary IS NULL, salary ASC, employee_id;

T-SQL has no boolean value to sort on, so on SQL Server the same rank is written as a CASE expression that returns 1 for a null salary and 0 otherwise. A report moved from PostgreSQL to MySQL comes back with a different row at the top, and no word of the query has changed. SQL NULL values covers the rest of what NULL does to a query.

Case-insensitive and custom sorting

Sorting text follows the column's collation rather than alphabetical order in the everyday sense. PostgreSQL 17's C and POSIX collations "sort by byte values rather than natural language order", which puts every ASCII uppercase letter ahead of every lowercase one, so a column collated C returns Zoe before adam. A linguistic collation, case-sensitive or not, keeps adam in its alphabetical place and lets case decide only between two spellings of the same word. Sorting on a lowercased copy of the value removes the difference either way:

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

When the order you want is neither alphabetical nor numeric, a CASE expression assigns each value a rank and the sort uses that:

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

Sales comes first because the CASE gives it 1, and the two HR rows fall to the ELSE branch together. The trailing name orders the rows inside each rank, without which the pairs could come back either way round.

Common mistakes and performance tips

The mistake behind most of the others is trusting an order nobody asked for:

• A query with no ORDER BY can come back in insert order on a small table and stop doing so once the table grows, gains an index, or is read by more than one worker.
• A sort inside a subquery or a CTE buys nothing, because the outer query is free to return its rows in any order it likes.
• Paging without a unique tie-breaker produces overlapping or missing pages from a query that looks sorted.
• ORDER BY LOWER(name) gets nothing from an index on name, and needs an index on the expression itself where the engine supports one.

SQL Server does not even accept a sort inside a subquery: the clause "isn't valid in views, inline functions, derived tables, and subqueries, unless you also specify either the TOP or OFFSET and FETCH clauses", and where it is allowed, it exists to decide which rows those clauses keep. Put the sort on the outermost SELECT:

WITH top_earners AS (
    SELECT name, salary
    FROM Employees
    WHERE salary > 4000
)
SELECT * FROM top_earners
ORDER BY salary DESC;

Nina is absent because salary > 4000 is unknown for a NULL salary, not because of the sort.

Sorting itself costs time, and where the rows come out of the table in the wrong order the engine has to hold the result and sort it. An index that already stores the rows in the order you ask for removes that step:

CREATE INDEX idx_employees_salary ON Employees (salary DESC);

Read the execution plan before and after adding an index rather than assuming, and EXPLAIN QUERY PLAN in SQLite is a small example of how that is done.

Use ORDER BY in DbSchema

Writing a sort against a schema you did not design means checking what the columns are and what they hold. DbSchema connects to the database, reverse-engineers it, and draws the tables as an interactive diagram with each column and its type, so the column you are about to sort on and its neighbours are visible at once.

1. Open the SQL Editor from the Editors menu.
2. Write the query and click Execute Query. DbSchema runs the statement at the cursor against the connected database and shows the result as a table, which is where you read whether the sort did what you meant.
3. Click Save in the result pane to write the full sorted result to a file. The query is re-executed and every row is exported, not only the rows on screen.

To sort data without writing SQL at all, hold Shift and Ctrl and click a table header in the diagram. That opens the Relational Data Editor inline, where the rows can be filtered and sorted directly. The Relational Data Editor is part of DbSchema Pro.

A statement in the SQL Editor runs against the live database, while the editor itself is saved inside the design model file, which is a Pro edition feature.

A sort list that ends with the primary key returns the same rows in the same order on every run, whatever the table does in between. Download DbSchema at https://dbschema.com/download.html, connect to your database, and run your sort in the SQL Editor beside the diagram; both are in the free Community Edition.