What is ORDER BY in SQL? Sorting query results for beginners

ORDER BY is the SQL command for sorting query results. Without it, the database returns rows in whatever order it pleases — which looks like chaos to the user. With ORDER BY, you say explicitly: "sort by this column from low to high" (or the other way around).

Analogy: imagine a stack of books on your desk, tossed in a random pile. ORDER BY title means "arrange them alphabetically by title". ORDER BY year DESC means "newest first, oldest last".

Why ORDER BY matters

Without sorting, SQL gives you no guarantees about row order. The same query run twice in a row may return rows in different orders. So if order matters in your result (top-10 sales, news by date, leaderboards) — ORDER BY is mandatory.

Typical scenarios:

• UI lists: show products from cheapest to most expensive
• Rankings: top-10 users by points
• Timelines: posts from newest to oldest
• Alphabetical lookups: city list from A to Z

Basic syntax

SELECT *
FROM table_name
ORDER BY column_name [ASC | DESC];

• ASC — ascending (low to high). This is the default — you can omit it.
• DESC — descending (high to low).

Example: a library books table.

SELECT title, year
FROM books
ORDER BY year DESC;

This lists books from newest to oldest.

Example: a library

books table:

id	title	author	year	rating
1	War and Peace	Leo Tolstoy	1869	4.8
2	The Master and Margarita	Mikhail Bulgakov	1967	4.9
3	And Quiet Flows the Don	Mikhail Sholokhov	1940	4.7
4	Crime and Punishment	Fyodor Dostoevsky	1866	4.8

Query — sort by year of publication (ascending):

SELECT title, year FROM books ORDER BY year;

Result:

title	year
Crime and Punishment	1866
War and Peace	1869
And Quiet Flows the Don	1940
The Master and Margarita	1967

Oldest at the top. Switch to DESC and the order flips — newest first.

Sorting by multiple columns

Often one column isn't enough. Same year of publication, then break the tie by rating. Just list them with commas:

SELECT title, year, rating
FROM books
ORDER BY year DESC, rating DESC;

How it works:

1. The database first sorts by year DESC (newest on top).
2. Within rows that share the same year, it sorts by rating DESC.
3. If there were a third field, it would tie-break inside identical year+rating combos.

This is called "multi-level sorting". Very common: "first by date, then by priority within a date", "first by status, then by amount within a status".

Multi-level example. employees table:

id	name	department	salary
1	Anna	IT	4000
2	Boris	Sales	2700
3	Vera	IT	5000
4	Grisha	HR	2300
5	Denis	IT	4000
6	Elena	Sales	3200

Query: first by department (alphabetical), then within department by salary (high to low).

SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Result:

name	department	salary
Grisha	HR	2300
Vera	IT	5000
Anna	IT	4000
Denis	IT	4000
Elena	Sales	3200
Boris	Sales	2700

Departments alphabetical: HR → IT → Sales. Inside IT — Vera (5000), then Anna and Denis (both 4000, undefined order between them), and so on.

Want a deterministic order between Anna and Denis? Add a third tier, e.g. by name: ORDER BY department ASC, salary DESC, name ASC.

Sorting by an expression

ORDER BY accepts not just column names but expressions too:

SELECT title, likes, views
FROM posts
ORDER BY likes::float / views DESC;

This sorts posts by "engagement rate" — likes divided by views. The table has no such column, but the database computes it on the fly.

Another common pattern — using functions:

SELECT name FROM users ORDER BY LOWER(name);

LOWER lowercases the name for case-insensitive sorting.

Top-N: ORDER BY + LIMIT

The classic combo. To get "top 3 posts by likes", you sort first, then truncate:

SELECT title, likes
FROM posts
ORDER BY likes DESC
LIMIT 3;

First ORDER BY likes DESC puts the most-liked on top, then LIMIT 3 keeps the first three. Without ORDER BY, LIMIT 3 returns three arbitrary rows — a classic beginner bug.

NULLs in sorting

If a column has NULL values, databases differ on where to put them:

• PostgreSQL: ASC — NULLs at the end; DESC — at the beginning.
• MySQL: ASC — NULLs at the beginning; DESC — at the end.

If you need to be explicit, PostgreSQL has NULLS FIRST / NULLS LAST:

SELECT title, published_at
FROM posts
ORDER BY published_at DESC NULLS LAST;

This gives: fresh posts first, drafts (no publish date) at the very end. MySQL has no such syntax — you have to fake it with an expression: ORDER BY published_at IS NULL, published_at DESC.

Bigger example: before and after

orders table:

id	customer	amount	status	created_at
1	Anna	500	paid	2024-03-10
2	Boris	1500	paid	2024-03-15
3	Vera	200	cancelled	2024-03-12
4	Grisha	3000	paid	2024-03-08
5	Denis	800	pending	2024-03-15

Goal: "paid first, then pending, then cancelled. Within each group, newest at the top; ties broken by larger amount first."

SELECT id, customer, status, amount, created_at
FROM orders
ORDER BY
  CASE status
    WHEN 'paid'      THEN 1
    WHEN 'pending'   THEN 2
    WHEN 'cancelled' THEN 3
  END,
  created_at DESC,
  amount DESC;

Result:

id	customer	status	amount	created_at
2	Boris	paid	1500	2024-03-15
1	Anna	paid	500	2024-03-10
4	Grisha	paid	3000	2024-03-08
5	Denis	pending	800	2024-03-15
3	Vera	cancelled	200	2024-03-12

CASE is how you express a custom order for a string column. Alphabetically, cancelled would come before paid — but we care about meaning, not alphabet.

Common beginner mistakes

1. Forgetting that without ORDER BY, order is not guaranteed. People assume "the database keeps insertion order". It doesn't — the order can be anything and may change between runs. Need an order? Write ORDER BY.

2. ORDER BY before FROM. The clause order is fixed: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT. ORDER BY always sits near the end.

3. Sorting by a column not in SELECT. Most DBs allow this — you can sort by any column from the table even if it's not in the output. Exception: when you SELECT DISTINCT, sorting by a column not in the output errors out, because DISTINCT throws away the data needed for sorting.

4. ORDER BY 1, 2. You can sort by column ordinal in SELECT — 1 is the first column, 2 the second. Works, but reads poorly. Use names.

5. ORDER BY and UNION. When you UNION two queries, ORDER BY is written once at the very end and applies to the combined result. You can't put it inside individual UNION parts.

6. Numbers as strings. If a VARCHAR column stores numbers ("10", "2", "100"), sorting goes alphabetically, not numerically: "10", "100", "2". Fix by casting ORDER BY CAST(col AS INTEGER), or store numbers as numbers in the first place.

Quick recap

• ORDER BY sorts the query result.
• ASC (default) is ascending, DESC is descending.
• Sort by multiple columns with commas — multi-level sort.
• Expressions and functions are valid in ORDER BY, not just column names.
• Without ORDER BY, no order is guaranteed.
• Combined with LIMIT it gives you top-N.
• Watch out for NULLs — databases place them differently.

Try it yourself

On the books table:

1. List books from highest rating to lowest.
2. Sort by author (alphabetical), then within an author by year (oldest to newest).
3. Top-3 books by rating (combo ORDER BY + LIMIT).

Write the queries on your own first. Sorting is one of the most intuitive SQL operations — you'll get it fast.