ORDER BY feels like the simplest clause in SQL right up until it isn’t — until you’re trying to sort NULLs a specific way, or sort by a computed value, or sort a huge table efficiently without tanking performance. I’ve been caught off guard by all three of those at different points, so I wanted to put together the guide I wish I’d had: not just “here’s the basic syntax,” but every practical detail that actually comes up when you use ORDER BY in real PostgreSQL work.
What Does ORDER BY Do?
By default, PostgreSQL does not guarantee any particular order for the rows returned by a query — unless you explicitly sort them. ORDER BY tells PostgreSQL exactly how to arrange the final result set, based on one or more columns or expressions.
This surprises people coming from spreadsheets or simpler tools: without ORDER BY, the order rows come back in is effectively undefined and can change between runs, especially as data grows or the query planner picks a different execution strategy. If order matters to your application at all, ORDER BY isn’t optional.
Setting Up an Example
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
price NUMERIC(10,2),
stock INT,
created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO products (name, price, stock) VALUES
('Wireless Mouse', 25.00, 40),
('Mechanical Keyboard', 85.00, NULL),
('USB-C Hub', 45.00, 15),
('Laptop Stand', 30.00, 0),
('Webcam', 60.00, 8);
Basic Syntax
SELECT name, price
FROM products
ORDER BY price;
By default, ORDER BY sorts in ascending order (smallest to largest, A to Z, earliest to latest). To reverse it:
SELECT name, price
FROM products
ORDER BY price DESC;
ASC is the implicit default, but I sometimes write it explicitly anyway for clarity in queries where the sort direction really matters to the reader.
Sorting by Multiple Columns
SELECT name, price, stock
FROM products
ORDER BY stock DESC, price ASC;
This sorts primarily by stock (highest first), and for products with equal stock levels, sorts by price (lowest first) as a tiebreaker. Each column in ORDER BY can have its own independent direction — you’re not locked into a single direction for the whole clause.
Sorting by Column Position
You can reference columns by their position in the SELECT list instead of by name:
SELECT name, price
FROM products
ORDER BY 2 DESC;
This sorts by price (the second column selected). I generally avoid this in production code — it’s fragile, since reordering your SELECT list silently changes the sort behavior — but you’ll see it in older codebases and it’s worth recognizing.
Sorting by an Expression
You don’t need the sorted value to appear in your SELECT list at all — you can sort by any valid expression:
SELECT name, price, stock
FROM products
ORDER BY price * stock DESC;
This sorts by total inventory value (price times stock) even though that calculated value isn’t one of the selected columns.
Sorting by a Column Alias
SELECT name, price, (price * 1.15) AS price_with_tax
FROM products
ORDER BY price_with_tax DESC;
PostgreSQL allows referencing a SELECT list alias directly in ORDER BY, which is convenient and avoids repeating the full expression.
Handling NULLs in ORDER BY
This is the detail that catches almost everyone off guard at some point. By default, PostgreSQL sorts NULL values as larger than any non-null value in ascending order — meaning NULLs appear last in ASC sorts and first in DESC sorts.
SELECT name, stock
FROM products
ORDER BY stock ASC;
Here, Mechanical Keyboard (with stock = NULL) appears at the very end, even though logically you might expect it to appear first, given that its stock level is literally unknown.
If you want explicit control over NULL placement, PostgreSQL gives you NULLS FIRST and NULLS LAST:
SELECT name, stock
FROM products
ORDER BY stock ASC NULLS FIRST;
SELECT name, stock
FROM products
ORDER BY stock DESC NULLS LAST;
I use NULLS LAST a lot in dashboards where I want complete, known data to appear first, pushing unknowns to the bottom regardless of sort direction.
Case-Sensitivity in Text Sorting
By default, PostgreSQL’s text sorting follows your database’s collation settings, which are often case-sensitive in a way that puts all uppercase letters before all lowercase letters ("Apple" before "banana", even though alphabetically you might expect them interleaved).
For a case-insensitive sort:
SELECT name FROM products
ORDER BY LOWER(name);
This is a small thing, but it matters a lot for user-facing lists — nobody wants to see “Zebra” sorted before “apple” just because of capitalization.
ORDER BY With LIMIT and OFFSET
ORDER BY is almost always paired with LIMIT for “top N” queries:
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;
This gives me the three most expensive products. Combined with OFFSET, this becomes the classic pagination pattern:
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 10 OFFSET 20;
This skips the first 20 rows and returns the next 10 — page 3 of a 10-item-per-page listing, assuming a stable sort order. It’s worth noting: LIMIT/OFFSET pagination without a deterministic ORDER BY (one that fully disambiguates ties, often by including a unique column like id) can return inconsistent results across pages if multiple rows share the same sort value.
SELECT name, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 10 OFFSET 20;
Adding id as a tiebreaker guarantees a stable, repeatable order even when many rows share the same price.
ORDER BY With GROUP BY
ORDER BY runs after grouping and aggregation, so you can sort by aggregated values directly:
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
ORDER BY product_count DESC;
This works whether or not category exists in this particular table — the point is that ORDER BY can reference the aggregate result column just like any other selected column.
ORDER BY With UNION
When combining result sets with UNION, ORDER BY can only be applied once, at the very end of the entire combined statement, using the column names from the first SELECT:
SELECT name, price FROM products WHERE stock > 0
UNION ALL
SELECT name, price FROM products WHERE stock = 0
ORDER BY price DESC;
You cannot put a separate ORDER BY inside each individual SELECT within a UNION — PostgreSQL will either reject it or ignore it depending on context, so keep the single ORDER BY at the end.
Performance Considerations
Sorting large result sets can be expensive, especially if PostgreSQL has to sort in memory or spill to disk. An index on the sorted column can let PostgreSQL avoid a separate sort step entirely by reading rows already in order:
CREATE INDEX idx_products_price ON products (price);
Check with EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT name, price FROM products ORDER BY price DESC;
If you see a Sort node in the plan on a large table, and this query runs frequently, an index matching the sort order (including direction, if consistently used) can turn that into an Index Scan that skips the explicit sort step entirely.
Custom Sort Orders With CASE
Sometimes the sort order you need doesn’t match any natural alphabetical or numeric ordering. A common example: sorting order statuses in a specific business-meaningful sequence rather than alphabetically.
SELECT name, status
FROM orders
ORDER BY
CASE status
WHEN 'urgent' THEN 1
WHEN 'processing' THEN 2
WHEN 'shipped' THEN 3
WHEN 'delivered' THEN 4
ELSE 5
END;
This puts urgent orders first, then processing, and so on — an order that has nothing to do with alphabetical sorting but matches how a support team would actually want to triage a queue. I use this CASE-based custom sort pattern constantly for dashboards where the “natural” sort order genuinely isn’t alphabetical or numeric.
An alternative for a fixed, reusable custom order is to define an actual PostgreSQL ENUM type with values in the desired order — PostgreSQL sorts enum values by their declared order automatically, without needing a CASE expression at all:
CREATE TYPE order_status AS ENUM ('urgent', 'processing', 'shipped', 'delivered');
ALTER TABLE orders ALTER COLUMN status TYPE order_status USING status::order_status;
SELECT name, status FROM orders ORDER BY status;
I’d reach for the ENUM approach when the custom order is a permanent, structural part of the data model, and the CASE approach for one-off reports where I don’t want to change the underlying schema.
ORDER BY and Window Functions
It’s worth knowing that ORDER BY shows up in a second, entirely different context: inside window functions, where it controls the order rows are processed within a partition, completely independent of the final output order of the query.
SELECT
name,
price,
ROW_NUMBER() OVER (ORDER BY price DESC) AS price_rank
FROM products
ORDER BY price_rank;
The ORDER BY price DESC inside OVER (...) determines how ROW_NUMBER() assigns ranking numbers. The ORDER BY price_rank at the very end of the query is a completely separate, standard ORDER BY controlling the final row order of the output. It’s easy to conflate these two uses of the same keyword when you’re first learning window functions, but they’re solving different problems — one ranks/processes rows within logical groups, the other arranges the final result set you actually see.
Collation-Aware Sorting for Multiple Languages
If your application handles text in more than one language or script, sorting can get more nuanced than a simple case-insensitive fix. PostgreSQL lets you specify a collation directly in ORDER BY, which controls exactly how characters are compared and ranked:
SELECT name FROM products
ORDER BY name COLLATE "en_US";
This tells PostgreSQL to sort using English (US) collation rules specifically, regardless of the database’s default collation setting. This matters more than people expect once you’re dealing with accented characters, non-Latin scripts, or locale-specific conventions — for example, whether “ä” sorts near “a” or at the very end of the alphabet depends entirely on collation rules, not on some universal standard.
SELECT name FROM products
ORDER BY name COLLATE "de_DE";
German collation, for instance, handles umlauts differently than English collation does. If your application serves users in multiple locales and each locale expects text sorted according to its own conventions, you may need to apply the appropriate collation per query, or per user session, rather than relying on a single database-wide default. I ran into this directly while working on a multi-region e-commerce feature — product names sorted correctly for English-speaking users but looked subtly wrong to German-speaking users until I added the explicit collation.
Sorting Nulls Alongside Custom Business Logic
Combining NULLS FIRST/NULLS LAST with a CASE-based custom sort is a pattern worth knowing for genuinely complex real-world sorting needs:
SELECT name, priority_score
FROM tasks
ORDER BY
CASE WHEN priority_score IS NULL THEN 1 ELSE 0 END,
priority_score DESC NULLS LAST;
This is a slightly belt-and-suspenders approach — the CASE expression explicitly pushes NULL-priority tasks to the very end as a separate sorting tier, while the NULLS LAST handles the remaining tie-breaking. In practice you often only need one or the other, but understanding both mechanisms means you’re never stuck when a stakeholder asks for a sort order that doesn’t map cleanly onto a single column’s natural ordering.
Common Use Cases
- “Top N” queries: highest-priced products, most recent orders, top customers by spend.
- Pagination: combined with
LIMIT/OFFSETfor paged listings. - Leaderboards and rankings: sorting by score, count, or calculated metrics.
- Chronological feeds: sorting by
created_atfor activity timelines. - Alphabetical listings: sorting names or titles for user-facing lists, often case-insensitively.
Troubleshooting Tips
My results seem to be in a different order each time I run the same query. This means you don’t have a fully deterministic ORDER BY — if multiple rows share the same sort value, their relative order isn’t guaranteed. Add a unique tiebreaker column (like id) to make the sort fully deterministic.
NULLs are showing up in an unexpected position. Remember NULLs sort as “largest” by default. Use NULLS FIRST or NULLS LAST explicitly if the default behavior doesn’t match what you need.
Text sorting looks wrong (uppercase before lowercase, etc.). This is your collation setting. Use LOWER() or UPPER() in ORDER BY for a case-insensitive sort, or consider a case-insensitive collation for the column if this is a persistent need across many queries.
Pagination is returning duplicate or skipped rows across pages. This almost always means the ORDER BY isn’t fully deterministic. Add a unique column as a final tiebreaker to guarantee stable ordering across LIMIT/OFFSET pages.
Sorting a huge table is slow. Check EXPLAIN ANALYZE for a Sort step, and consider adding an index on the sorted column(s) that matches your query’s direction, so PostgreSQL can read rows pre-sorted instead of sorting them at query time.
Best Practices I Follow
- Never assume row order without an explicit
ORDER BY— PostgreSQL doesn’t guarantee it otherwise. - Add a unique tiebreaker column for any query paired with
LIMIT/OFFSETpagination. - Use
NULLS FIRST/NULLS LASTexplicitly whenever NULL placement actually matters to your use case. - Sort text case-insensitively for user-facing lists using
LOWER()or an appropriate collation. - Index columns used for frequent, large-table sorts to avoid expensive sort operations at query time.
- Avoid sorting by column position in production code — reference column names or aliases instead for clarity and safety.
Frequently Asked Questions
Is ASC the default sort direction? Yes, if you don’t specify ASC or DESC, PostgreSQL sorts in ascending order by default.
Where do NULLs go by default in ORDER BY? NULLs sort as if they were larger than any other value — meaning they appear last in ASC order and first in DESC order, unless you override this with NULLS FIRST/NULLS LAST.
Can I ORDER BY a column that isn’t in my SELECT list? Yes, for a simple query you can sort by any valid column or expression from the underlying tables, even if it’s not part of the output. This isn’t allowed, however, when using DISTINCT, UNION, or certain other constructs — in those cases, the sort expression generally must appear in the SELECT list.
Does ORDER BY run before or after LIMIT? ORDER BY runs first, arranging the full result set, and LIMIT/OFFSET then applies to that already-sorted set. This is why ORDER BY is essential for reliable “top N” queries.
Can I sort by multiple columns with different directions? Yes — each column in ORDER BY can independently specify ASC or DESC, for example: ORDER BY category ASC, price DESC.
Wrapping Up
ORDER BY looks trivial at first glance, but the details — NULL handling, deterministic tiebreakers, collation-aware text sorting, and indexing for performance — are exactly the kind of thing that separates a query that works in testing from one that behaves reliably in production at scale. Once you build the habit of always sorting deterministically and thinking about NULL placement explicitly, you’ll avoid an entire category of subtle, hard-to-reproduce bugs in pagination and reporting features down the line.