Key Takeaways

• ORDER BY controls the order of rows returned by a query.
• ASC sorts ascending and is the default.
• DESC sorts descending.
• Multiple sort columns provide tie-breakers.
• Without ORDER BY, row order is not guaranteed.
• NULL placement can differ by database system, so explicit control is safer when it matters.

In OSSQL.003: WHERE, we learned how to decide which rows survive a query. The next step is deciding what order those surviving rows should appear in. SQL uses ORDER BY for that job.

The official PostgreSQL 18 documentation is explicit: if sorting is not requested, returned row order is unspecified and must not be relied on. ORDER BY is what makes a particular output ordering intentional.

Learning Objectives

• Write a basic ORDER BY clause.
• Explain the difference between ASC and DESC.
• Sort by more than one column.
• Use a second sort column as a tie-breaker.
• Sort by a selected alias or expression where supported.
• Recognize why results without ORDER BY should not be assumed to have a stable order.
• Handle NULL placement deliberately when the database supports explicit NULLS FIRST or NULLS LAST.

The Basic ORDER BY Pattern

SELECT column1, column2
FROM table_name
ORDER BY column1;

Suppose a table named miners contains model names and hashrates. This query returns the rows from lowest to highest hashrate because ascending order is the default:

SELECT model, hashrate_th
FROM miners
ORDER BY hashrate_th;

You can write the same intent explicitly with ASC:

SELECT model, hashrate_th
FROM miners
ORDER BY hashrate_th ASC;

ASC Means Ascending

ASC places smaller values before larger values. For numbers, that usually means low to high. For text, it follows the database’s comparison and collation rules. For dates, earlier values normally come before later values.

SELECT name, temperature_c
FROM racks
ORDER BY temperature_c ASC;

This is useful when you want to see the coolest racks first, the lowest prices first, the earliest timestamps first, or names in ascending text order.

DESC Means Descending

DESC reverses the direction. Larger values come before smaller ones, later dates can appear before earlier dates, and high measurements can be placed at the top of the result.

SELECT model, hashrate_th
FROM miners
ORDER BY hashrate_th DESC;

A common operational query is to sort hottest equipment first so the most urgent rows appear at the top:

SELECT rack_id, temperature_c
FROM racks
ORDER BY temperature_c DESC;

ORDER BY Works After Filtering

WHERE and ORDER BY solve different problems. WHERE decides which rows remain; ORDER BY arranges the rows that remain.

SELECT model, hashrate_th, status
FROM miners
WHERE status = 'online'
ORDER BY hashrate_th DESC;

This query first limits the result to online miners and then places the highest-hashrate machines first. That progression directly combines the filtering skills from OSSQL.003 with today’s sorting clause.

Sorting by Multiple Columns

You can list more than one sort expression. SQL sorts by the first expression, then uses the next expression to resolve ties within the first one.

SELECT site, rack, temperature_c
FROM rack_readings
ORDER BY site ASC, rack ASC;

Rows are grouped into ascending site order. Within each site, rows are then ordered by rack. This gives the result a predictable hierarchy.

SELECT manufacturer, model, hashrate_th
FROM miners
ORDER BY manufacturer ASC, hashrate_th DESC;

Here, manufacturers are sorted ascending. Within each manufacturer, higher-hashrate models appear first. PostgreSQL documents this exact principle: later sort expressions break ties created by earlier expressions.

Tie-Breakers Matter

If two rows have equal values for every expression in the ORDER BY list, their relative order can still be unspecified. Add a unique or nearly unique final sort key when repeatable ordering matters.

SELECT miner_id, model, temperature_c
FROM miners
ORDER BY temperature_c DESC, miner_id ASC;

If two miners have the same temperature, miner_id becomes the tie-breaker. This is especially useful for reports, pagination, exports, dashboards, and automated tests where changing row order can create confusing results.

Sorting by an Alias

Many SQL systems allow an output alias to be referenced in ORDER BY. PostgreSQL supports this for a standalone output column label.

SELECT model,
       hashrate_th / power_kw AS th_per_kw
FROM miners
ORDER BY th_per_kw DESC;

This query calculates a result column named th_per_kw and then sorts by that calculated output. Alias rules can vary around more complex expressions, so use your database’s documentation when the expression becomes advanced.

NULL Placement Needs Attention

NULL represents a missing or unknown value, and databases do not all treat its default sort position the same way. PostgreSQL supports explicit NULLS FIRST and NULLS LAST modifiers. Current SQLite also supports those modifiers, but its default NULL behavior differs from PostgreSQL.

SELECT name, last_service_date
FROM equipment
ORDER BY last_service_date ASC NULLS LAST;

This is clearer than relying on a database-specific default when the location of missing values matters to the result.

Do Not Trust Natural Row Order

A table may appear to return rows in insertion order during one test, then return them differently after an index change, database update, query-plan change, maintenance operation, or different execution path. PostgreSQL and SQLite both document that row order is undefined when no ORDER BY controls it.

The technician-level rule is simple: if row order matters to the meaning of the result, write the sort explicitly.

ORDER BY and Performance

Sorting can require the database to compare many rows and build an ordered result. PostgreSQL notes that a matching B-tree index may sometimes provide rows in the requested order without a separate sort step. Whether an index or explicit sort is faster depends on the query and how much data must be read.

For a beginner, the priority is correctness. First write the query so it returns the intended rows in the intended order. Later lessons can examine indexing and query plans in more depth.

Common Beginner Mistakes

• Assuming rows already have a permanent order: without ORDER BY, do not rely on it.
• Forgetting that ASC is the default: ORDER BY temperature_c means ascending unless another direction is specified.
• Applying DESC to the wrong column: each sort expression controls its own direction.
• Using only one sort key when ties matter: add a second or third key when stable ordering is important.
• Assuming NULL sorts identically everywhere: check the database or specify placement explicitly.
• Confusing filtering and sorting: WHERE removes rows; ORDER BY rearranges rows.
• Using column positions carelessly: some systems allow ORDER BY 1, but explicit column names are usually easier to maintain.

Hands-On Exercise

Use the same small practice database from OSSQL.001: Relational Databases and Tables and the column-selection skills from OSSQL.002: SELECT.

1. Return all rows sorted by one numeric column ascending.
2. Run the same query descending.
3. Sort a text column alphabetically.
4. Sort a date column newest first.
5. Filter the rows with WHERE, then sort the remaining rows.
6. Sort by two columns and explain which one is the tie-breaker.
7. Add a unique ID as the final tie-breaker.
8. If your database supports it, sort a nullable column with explicit NULLS LAST.

Knowledge Check

1. What clause controls the order of returned rows?
2. What sort direction is used when neither ASC nor DESC is written?
3. What does DESC do?
4. How are multiple ORDER BY expressions used?
5. Why can a unique final sort key be useful?
6. Does WHERE sort rows?
7. Can a query’s apparent natural order be relied on without ORDER BY?
8. Why might NULLS FIRST or NULLS LAST be preferable to accepting a default?

Answers

1. ORDER BY.
2. Ascending order, ASC.
3. It sorts that expression in descending order.
4. The first expression sorts the main groups; later expressions resolve ties from earlier ones.
5. It makes equal-value rows resolve into a repeatable order.
6. No. WHERE filters rows.
7. No. Without ORDER BY, row order is not guaranteed.
8. Because default NULL placement differs across database systems and explicit intent is easier to understand and maintain.

Elementary Review

ORDER BY is SQL’s row-sorting clause. Use ASC for low-to-high or earlier-to-later order, DESC for the reverse, and add more sort expressions when you need tie-breakers. If the order matters, do not trust the database to choose it for you.

Next SQL Lesson

The next SQL lesson will introduce LIMIT and OFFSET, showing how to return only part of an ordered result and why deterministic sorting becomes especially important for pagination.

Primary References

• PostgreSQL 18 — Sorting Rows (ORDER BY)
• SQLite — SELECT and ORDER BY

BitcoinVersus.Tech provides technical education and analysis for informational purposes only.

BitcoinVersus.Tech Editor’s Note:
We volunteer daily to ensure the credibility of the information on this platform is Verifiably True. If you would like to support to help further secure the integrity of our research initiatives, please donate using the QR code provided

For best QR-code scanning results, please scan this donation code from a desktop or larger screen. Mobile screens may prevent camera recognition.