By Matthew Rathbone · November 17, 2026

The SQL ORDER BY clause sorts the rows a query returns. Without it, the database hands rows back in whatever order is cheapest for it, and that order can change from one run to the next. Add ORDER BY whenever the order matters: the highest salaries, the newest orders, names A to Z.

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

ASC (ascending: smallest first, A to Z, oldest date first) is the default. DESC sorts descending: largest first, Z to A, newest date first. ORDER BY is always the last clause in a query, apart from LIMIT/OFFSET.

Jump to: sample data · ascending · descending · multiple columns · ties · NULLs first or last · sorting text · dates · expressions and aliases · ORDER BY 1 · custom order with CASE · top N rows · with GROUP BY · order of execution · database differences · performance · mistakes · FAQ

Sample data

Every query below was run on SQLite 3.45 and the output is copied from the real result. Where databases behave differently (NULL placement, case sensitivity, errors) the output comes from PostgreSQL 16, and the difference is called out.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY, name TEXT, department TEXT,
  salary INTEGER, hired DATE, bonus INTEGER, status TEXT
);
INSERT INTO employees VALUES
 (1, 'Maya',  'Sales',        62000, '2021-03-15', 5000, 'active'),
 (2, 'Leo',   'Engineering',  98000, '2019-07-01', NULL, 'active'),
 (3, 'Priya', 'Engineering', 105000, '2022-01-10', 8000, 'on leave'),
 (4, 'Sam',   'Support',      48000, '2023-05-22', NULL, 'active'),
 (5, 'Alice', 'Sales',        71000, '2018-11-30', 3000, 'inactive'),
 (6, 'Jonas', 'Support',      48000, '2020-09-14', 1000, 'active'),
 (7, 'bella', 'Engineering',  87000, '2024-02-05', 2000, 'active');

Three details will matter later: Sam and Jonas earn the same salary, Leo and Sam have no bonus (NULL), and "bella" is written in lowercase.

ORDER BY ascending (ASC)

Sort employees from the lowest salary to the highest:

SELECT name, salary
FROM employees
ORDER BY salary;
name  | salary
------+-------
Sam   | 48000
Jonas | 48000
Maya  | 62000
Alice | 71000
bella | 87000
Leo   | 98000
Priya | 105000
(7 rows)

ORDER BY salary and ORDER BY salary ASC are identical. Most people leave ASC out; write it when you want the intent to be obvious next to a DESC column.

ORDER BY descending (DESC)

Put DESC after the column to flip the order, highest salary first:

SELECT name, salary
FROM employees
ORDER BY salary DESC;
name  | salary
------+-------
Priya | 105000
Leo   | 98000
bella | 87000
Alice | 71000
Maya  | 62000
Sam   | 48000
Jonas | 48000
(7 rows)

DESC applies only to the column right before it. That catches people out as soon as they sort by more than one column.

ORDER BY multiple columns

List columns separated by commas. The database sorts by the first column, and only uses the second column to break ties in the first, and so on. Here, departments go A to Z and, inside each department, the highest salary comes first:

SELECT name, department, salary
FROM employees
ORDER BY department, salary DESC;
name  | department  | salary
------+-------------+-------
Priya | Engineering | 105000
Leo   | Engineering | 98000
bella | Engineering | 87000
Alice | Sales       | 71000
Maya  | Sales       | 62000
Sam   | Support     | 48000
Jonas | Support     | 48000
(7 rows)

Each column gets its own direction. ORDER BY department, salary DESC means "department ascending, then salary descending". It does not mean both descending. To reverse both, write DESC twice:

SELECT name, department, salary
FROM employees
ORDER BY department DESC, salary;
name  | department  | salary
------+-------------+-------
Sam   | Support     | 48000
Jonas | Support     | 48000
Maya  | Sales       | 62000
Alice | Sales       | 71000
bella | Engineering | 87000
Leo   | Engineering | 98000
Priya | Engineering | 105000
(7 rows)

That one sorts departments Z to A and salaries low to high within each. The order of the columns in ORDER BY matters: ORDER BY salary, department would sort by salary first and only look at department when two salaries are equal.

Ties: make the order predictable

Sam and Jonas both earn 48,000. In the two results above, Sam came before Jonas, but nothing in the query asked for that. When rows tie on every ORDER BY column, the database may return them in any order, and the order can change after an update, a new index or a database upgrade. This is the cause of "pagination shows the same row twice" bugs.

Fix it by adding a column that is unique, such as the name or the primary key, as a final tie-breaker:

SELECT name, salary
FROM employees
ORDER BY salary DESC, name;
name  | salary
------+-------
Priya | 105000
Leo   | 98000
bella | 87000
Alice | 71000
Maya  | 62000
Jonas | 48000
Sam   | 48000
(7 rows)

Now Jonas comes before Sam every time. If you page through results with LIMIT and OFFSET, always end the ORDER BY with a unique column (id is the usual choice).

ORDER BY with NULL values: NULLS FIRST and NULLS LAST

Where NULL lands depends on the database. Sort by bonus in SQLite:

SELECT name, bonus
FROM employees
ORDER BY bonus;
name  | bonus
------+------
Leo   | NULL
Sam   | NULL
Jonas | 1000
bella | 2000
Alice | 3000
Maya  | 5000
Priya | 8000
(7 rows)

SQLite treats NULL as smaller than any value, so NULLs come first in ascending order and last in descending order. MySQL and SQL Server do the same. PostgreSQL and Oracle do the opposite: they treat NULL as larger than any value. Here is ORDER BY bonus DESC in PostgreSQL 16:

name  | bonus
------+------
Sam   | NULL
Leo   | NULL
Priya | 8000
Maya  | 5000
Alice | 3000
bella | 2000
Jonas | 1000
(7 rows)

"Top bonuses" that start with two people who have no bonus is rarely what you want. Add NULLS LAST (or NULLS FIRST) to choose explicitly:

SELECT name, bonus
FROM employees
ORDER BY bonus DESC NULLS LAST;
name  | bonus
------+------
Priya | 8000
Maya  | 5000
Alice | 3000
bella | 2000
Jonas | 1000
Leo   | NULL
Sam   | NULL
(7 rows)

NULLS FIRST / NULLS LAST works in PostgreSQL, Oracle, SQLite 3.30+, Snowflake and DuckDB. MySQL and SQL Server don't support it. There, sort on whether the value is NULL first:

-- MySQL, SQL Server, and everywhere else: NULLs last
SELECT name, bonus
FROM employees
ORDER BY CASE WHEN bonus IS NULL THEN 1 ELSE 0 END, bonus DESC;

That gives the same result as the NULLS LAST query. In MySQL and SQLite you can shorten the first part to ORDER BY bonus IS NULL, bonus DESC, because a true/false comparison is a 1 or a 0 there. If you'd rather treat a missing bonus as zero, sort by COALESCE(bonus, 0) instead.

DatabaseNULLs in ASCNULLs in DESCNULLS FIRST / LAST?
MySQL / MariaDBFirstLastNo
SQL ServerFirstLastNo
SQLiteFirstLastYes (3.30+)
PostgreSQLLastFirstYes
OracleLastFirstYes

Sorting text: alphabetical order and case sensitivity

Sort names A to Z:

SELECT name
FROM employees
ORDER BY name;
name
-----
Alice
Jonas
Leo
Maya
Priya
Sam
bella
(7 rows)

"bella" lands at the end. SQLite compares text byte by byte by default, and every uppercase letter sorts before every lowercase one. PostgreSQL does the same with a C collation, which is what this test database used. Text sorting is controlled by the column's collation, so the same query can give different results in different databases:

  • MySQL (default utf8mb4_0900_ai_ci) and SQL Server (default ..._CI_AS) are case-insensitive, so "bella" sorts between "Alice" and "Jonas".
  • PostgreSQL uses the collation the database was created with. An en_US collation puts "bella" after "Alice"; C puts it last.
  • SQLite is case-sensitive unless you ask otherwise.

To get case-insensitive alphabetical order everywhere, sort by the lowercase value:

SELECT name
FROM employees
ORDER BY LOWER(name);
name
-----
Alice
bella
Jonas
Leo
Maya
Priya
Sam
(7 rows)

SQLite also accepts ORDER BY name COLLATE NOCASE, and PostgreSQL accepts a named collation such as ORDER BY name COLLATE "en-x-icu". Both gave the same result as LOWER(name).

Numbers stored as text sort wrong

If a column holds numbers in a text type, they sort character by character, so '100' comes before '25':

-- code is a TEXT column holding '9', '100', '25', '3'
SELECT code FROM t ORDER BY code;                     -- 100, 25, 3, 9
SELECT code FROM t ORDER BY CAST(code AS INTEGER);    -- 3, 9, 25, 100

Cast to a number in ORDER BY, or better, store numbers in a numeric column.

ORDER BY date

Dates sort chronologically when they're stored in a date type (or as ISO text like 2024-02-05, as in SQLite). Newest first:

SELECT name, hired
FROM employees
ORDER BY hired DESC;
name  | hired
------+-----------
bella | 2024-02-05
Sam   | 2023-05-22
Priya | 2022-01-10
Maya  | 2021-03-15
Jonas | 2020-09-14
Leo   | 2019-07-01
Alice | 2018-11-30
(7 rows)

If dates are stored as text in a format like 15/03/2021, they sort by the day number, not by date. Convert them first (STR_TO_DATE in MySQL, TO_DATE in PostgreSQL and Oracle, CONVERT in SQL Server). For more, see ORDER BY date in MySQL, ORDER BY date in SQL Server and ordering by month name.

ORDER BY an expression or a column alias

You can sort by a calculation, and you can sort by a column alias defined in SELECT:

SELECT name, salary + COALESCE(bonus, 0) AS total_pay
FROM employees
ORDER BY total_pay DESC;
name  | total_pay
------+----------
Priya | 113000
Leo   | 98000
bella | 89000
Alice | 74000
Maya  | 67000
Jonas | 49000
Sam   | 48000
(7 rows)

ORDER BY is the only clause that can see SELECT aliases, because it runs after SELECT (see order of execution). WHERE total_pay > 50000 would fail with "column does not exist".

One PostgreSQL quirk: an alias works on its own, but not inside a larger expression. ORDER BY total_pay + 1 fails with column "total_pay" does not exist. Repeat the expression instead.

You can also sort by a column you don't select, such as SELECT name FROM employees ORDER BY hired. The exception is SELECT DISTINCT, covered in mistakes.

ORDER BY 1: sorting by column position

A number in ORDER BY means "the Nth column in the SELECT list":

SELECT name, department, salary
FROM employees
ORDER BY 3 DESC, 1;
name  | department  | salary
------+-------------+-------
Priya | Engineering | 105000
Leo   | Engineering | 98000
bella | Engineering | 87000
Alice | Sales       | 71000
Maya  | Sales       | 62000
Jonas | Support     | 48000
Sam   | Support     | 48000
(7 rows)

ORDER BY 3 DESC, 1 means "salary descending, then name". It's handy when typing ad-hoc queries, but avoid it in saved code: if someone adds a column to the SELECT list, the sort silently changes. A number past the end of the list is an error (PostgreSQL: ORDER BY position 4 is not in select list).

Custom sort order with CASE

Alphabetical order isn't always the order people want. To show active employees first, then those on leave, then inactive ones, map each value to a number with a CASE expression and sort by that:

SELECT name, status
FROM employees
ORDER BY CASE status
           WHEN 'active'   THEN 1
           WHEN 'on leave' THEN 2
           ELSE 3
         END,
         name;
name  | status
------+---------
Jonas | active
Leo   | active
Maya  | active
Sam   | active
bella | active
Priya | on leave
Alice | inactive
(7 rows)

The CASE doesn't have to appear in the output. MySQL also has a shortcut, ORDER BY FIELD(status, 'active', 'on leave', 'inactive'), but CASE works in every database. (Notice "bella" sorted after "Sam" again: that's the case-sensitive comparison from sorting text.)

The same trick lets you pin one row to the top, for example ORDER BY CASE WHEN department = 'Support' THEN 0 ELSE 1 END, name.

ORDER BY with LIMIT: get the top N rows

ORDER BY plus a row limit answers "top N" questions. The three highest-paid employees:

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
name  | salary
------+-------
Priya | 105000
Leo   | 98000
bella | 87000
(3 rows)

Add OFFSET to skip rows, which is how simple pagination works. ORDER BY salary DESC, id LIMIT 3 OFFSET 3 returns rows 4 to 6. The limit is applied after sorting, so without ORDER BY, LIMIT 3 gives you three arbitrary rows.

Each database spells the limit a little differently:

DatabaseTop 3 by salary
MySQL, PostgreSQL, SQLiteORDER BY salary DESC LIMIT 3
SQL ServerSELECT TOP 3 name, salary ... ORDER BY salary DESC
Standard SQL (PostgreSQL, Oracle 12c+, SQL Server 2012+, Db2)ORDER BY salary DESC OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY

SQL Server requires the OFFSET part with FETCH; PostgreSQL and Oracle let you write FETCH FIRST 3 ROWS ONLY on its own. More detail: LIMIT in MySQL, TOP in SQL Server and limiting results in T-SQL.

Keep ties with FETCH ... WITH TIES

"Top 6 salaries" is ambiguous when the 6th and 7th rows tie. In PostgreSQL 13+, Oracle and SQL Server (TOP 6 WITH TIES), you can include everyone who ties with the last row:

SELECT name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 6 ROWS WITH TIES;
name  | salary
------+-------
Priya | 105000
Leo   | 98000
bella | 87000
Alice | 71000
Maya  | 62000
Sam   | 48000
Jonas | 48000
(7 rows)

Six rows were asked for and seven came back, because Sam and Jonas share 6th place. For "top N per group" (the top earner in each department), LIMIT isn't enough; use ROW_NUMBER() with PARTITION BY, shown in the window functions guide.

ORDER BY with GROUP BY

GROUP BY builds one row per group, and ORDER BY sorts those grouped rows. You can sort by an aggregate or its alias. Departments by average salary, highest first:

SELECT department,
       COUNT(*)    AS headcount,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;
department  | headcount | avg_salary
------------+-----------+------------------
Engineering | 3         | 96666.66666666667
Sales       | 2         | 66500.0
Support     | 2         | 48000.0
(3 rows)

GROUP BY always comes before ORDER BY in the query. And GROUP BY does not sort the result for you: some databases happened to return grouped rows in order in the past, but none guarantee it. If you want sorted groups, add ORDER BY. See the SQL GROUP BY guide for grouping in depth.

Where ORDER BY runs: SQL order of execution

You write a query in one order, but the database runs it in another:

  1. FROM and JOIN
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT (aliases are created here)
  6. DISTINCT
  7. ORDER BY
  8. LIMIT / OFFSET / FETCH

This explains most ORDER BY rules: it can use SELECT aliases (they already exist), it sorts after filtering (so WHERE shrinks the work), and LIMIT picks rows from the already-sorted result. Filtering and sorting together looks like this:

SELECT name, department
FROM employees
WHERE department = 'Engineering'
ORDER BY hired;
name  | department
------+------------
Leo   | Engineering
Priya | Engineering
bella | Engineering
(3 rows)

Only the three engineers are sorted, oldest hire first, and hired doesn't need to be in the output.

ORDER BY in MySQL, PostgreSQL, SQL Server, Oracle and SQLite

The basic syntax (ORDER BY col [ASC|DESC], ...) is identical everywhere. The differences are at the edges:

FeatureMySQLPostgreSQLSQL ServerOracleSQLite
Default NULL position (ASC)FirstLastFirstLastFirst
NULLS FIRST/LASTNoYesNoYes3.30+
Text case-sensitive by defaultNoDepends on collationNoYesYes
Top NLIMITLIMIT / FETCHTOP / OFFSET FETCHFETCH FIRSTLIMIT
Sort by aliasYesYes (alone, not in expressions)YesYesYes
Sort by position (ORDER BY 1)YesYesYesYesYes
Random orderRAND()RANDOM()NEWID()DBMS_RANDOM.VALUERANDOM()
ORDER BY in a subquery or viewAllowed, may be ignoredAllowed, may be ignoredError unless TOP/OFFSETAllowedAllowed, may be ignored

For the full reference, see the MySQL sorting docs, PostgreSQL's sorting rows chapter, SQL Server's ORDER BY clause and SQLite's SELECT docs. SQLite has its own walkthrough in SQLite ORDER BY.

ORDER BY performance

Sorting needs the whole filtered result before the first row can be returned, so on big tables it can be the slowest part of a query. SQLite's query plan shows the extra step:

EXPLAIN QUERY PLAN
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;

SCAN employees
USE TEMP B-TREE FOR ORDER BY

"USE TEMP B-TREE FOR ORDER BY" means SQLite builds a temporary sorted structure. Add an index on the sort column and the sort disappears, because the index is already in order:

CREATE INDEX idx_employees_salary ON employees (salary);

EXPLAIN QUERY PLAN
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;

SCAN employees USING INDEX idx_employees_salary

A few practical rules:

  • ORDER BY ... LIMIT on an indexed column is cheap. The database reads the first N index entries and stops.
  • Sorting on an expression (LOWER(name), salary + bonus) can't use a plain index on the column. Some databases support indexes on expressions if you need it.
  • Large OFFSET values are slow, because the database still sorts and skips every earlier row. For deep pagination, filter on the last value seen instead: WHERE (salary, id) < (48000, 6) ORDER BY salary DESC, id DESC LIMIT 20.
  • ORDER BY RANDOM() sorts the entire table to pick a few rows. Fine for small tables, slow for big ones.
  • Only sort in the outermost query. Sorting inside a subquery usually wastes work, and the outer query is free to ignore it.

More tips in how to improve SQL query performance.

Common ORDER BY mistakes

  1. Assuming rows come back in insert order. They don't have to. No ORDER BY means no guaranteed order, even if it looks sorted today.
  2. Expecting DESC to apply to every column. In ORDER BY department, salary DESC, only salary is descending. (Example)
  3. No tie-breaker when paginating. Tied rows can swap places between pages. End with a unique column. (Example)
  4. Forgetting where NULLs go. PostgreSQL and Oracle put NULLs first in DESC order. Use NULLS LAST or a CASE. (Example)
  5. Putting ORDER BY before WHERE or GROUP BY. The clause order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Anything else is a syntax error.
  6. Sorting a SELECT DISTINCT by a column that isn't selected. SELECT DISTINCT department FROM employees ORDER BY salary fails in PostgreSQL with for SELECT DISTINCT, ORDER BY expressions must appear in select list, because each department has several salaries. Sort by a selected column or an aggregate. The DISTINCT guide explains why.
  7. Adding ORDER BY to each part of a UNION. Only one ORDER BY is allowed, at the very end, and it sorts the combined result.
  8. Sorting numbers or dates stored as text. '100' sorts before '25'. Cast them, or fix the column type. (Example)

FAQ

What does ORDER BY do in SQL?

It sorts the rows in a query's result by one or more columns or expressions, in ascending (ASC) or descending (DESC) order. It doesn't change the data in the table, only the order the rows are returned in.

Is ORDER BY ascending or descending by default?

Ascending. ORDER BY salary is the same as ORDER BY salary ASC: smallest numbers, earliest dates and A-to-Z text first.

How do I ORDER BY descending in SQL?

Put DESC after the column: ORDER BY salary DESC. With several columns, add DESC to each one you want reversed.

How do I ORDER BY multiple columns?

Separate them with commas: ORDER BY department, salary DESC. Rows are sorted by the first column, and later columns only break ties.

Which comes first, ORDER BY or GROUP BY?

GROUP BY comes first, then HAVING, then ORDER BY. The full order is SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT.

What is the difference between ORDER BY and GROUP BY?

GROUP BY combines rows that share a value into one row per group, usually with an aggregate like COUNT or SUM. ORDER BY only sorts rows and never combines them. They're often used together: group, then sort the groups.

What does ORDER BY 1 mean in SQL?

Sort by the first column in the SELECT list. ORDER BY 2 DESC sorts by the second column, descending. It's a shorthand for interactive queries; name the column in code you'll keep.

Can I ORDER BY a column that isn't in the SELECT?

Yes, in a normal query. The exceptions are SELECT DISTINCT and UNION queries, where the sort column must be in the output.

How do I put NULL values last?

In PostgreSQL, Oracle and SQLite, add NULLS LAST: ORDER BY bonus DESC NULLS LAST. In MySQL and SQL Server, sort by a CASE first: ORDER BY CASE WHEN bonus IS NULL THEN 1 ELSE 0 END, bonus DESC.

Does ORDER BY slow down a query?

It can on large results, since the database must sort before returning rows. An index on the sort column lets the database skip the sort, and ORDER BY ... LIMIT on an indexed column is fast.

Practice ORDER BY

The free interactive ORDER BY lesson runs in your browser with no signup, and the next one, LIMIT, combines sorting with top-N queries. After that, try GROUP BY and sort the groups you build. The SQL practice problems and SQL query examples use ORDER BY constantly, and "find the top N" questions show up in almost every SQL interview. Keep the SQL cheat sheet open for the syntax.

When you're ready to sort data in your own database, Beekeeper Studio is a free, open source SQL editor for PostgreSQL, MySQL, SQLite and SQL Server.