SQL ORDER BY and LIMIT: Sorting Query Results (2026)

Quick Answer: What Do ORDER BY and LIMIT Do?

The Top-10 Report That Lied

A familiar mistake in early dashboards: someone builds a "top 10 customers by revenue" query as SELECT customer_id, revenue FROM customers LIMIT 10; — no ORDER BY. It runs without error, returns exactly 10 rows, and gets pasted straight into a report. The problem: those 10 rows are whatever the database happened to scan first, not the ten highest revenue customers. Nobody notices until someone cross-checks the numbers against a different tool and the top customer is missing entirely.

LIMIT trims rows. It has no opinion about which rows those are — that decision belongs entirely to ORDER BY. Skip the sort, and LIMIT just hands you an arbitrary, unstable sample that happens to have the right row count.

ASC and DESC: Setting the Sort Direction

ORDER BY takes a column name and an optional direction. Ascending (ASC) is the default — smallest number first, earliest date first, "A" before "Z."

-- smallest total first (ASC is implicit)
SELECT order_id, order_total
FROM orders
ORDER BY order_total;

-- highest total first
SELECT order_id, order_total
FROM orders
ORDER BY order_total DESC;

For a genuine "top N" query — top 10 customers, most recent 20 orders — DESC is almost always what you want, since "top" implies largest or most recent first. Leaving off DESC when you meant it is a quiet, easy-to-miss bug: the query runs fine and returns real rows, just the wrong end of the list.

Sorting by Multiple Columns

List more than one column in ORDER BY, separated by commas, and each column after the first acts as a tie-breaker — it only matters where the columns before it are equal.

-- group by country alphabetically, then highest spenders first within each country
SELECT customer_id, country, order_total
FROM orders
ORDER BY country ASC, order_total DESC;

Each column can have its own independent direction — mixing ASC and DESC in the same ORDER BY, like above, is completely normal and often exactly what a real report needs: alphabetical grouping with a meaningful sort inside each group.

Where NULLs Land When Sorting

NULL doesn't have a numeric or alphabetical value, so databases need a rule for where it goes in a sort — and that default rule is not the same everywhere.

-- PostgreSQL / BigQuery: force NULLs to the bottom regardless of default
SELECT customer_id, last_purchase_date
FROM customers
ORDER BY last_purchase_date DESC NULLS LAST;

If a report depends on where missing values end up — for example, customers who never purchased shouldn't dominate the top of a "most recent purchase" list — never rely on the database default. Set NULLS FIRST or NULLS LAST explicitly wherever the syntax supports it.

LIMIT: Capping How Many Rows Come Back

LIMIT restricts the result set to a fixed number of rows, applied after sorting is complete.

-- true top 10 by revenue — ORDER BY makes LIMIT meaningful
SELECT customer_id, revenue
FROM customers
ORDER BY revenue DESC
LIMIT 10;

Even outside a top-N report, LIMIT is worth using habitually during exploration — pairing it with any new or unfamiliar query caps how much data you pull back while you're still checking whether the logic is right, which matters most exactly on the tables where a mistake would otherwise be expensive.

OFFSET and the Pagination Trap

OFFSET skips a number of rows before returning results — the standard way to build "page 2, page 3" pagination.

-- page 3, 20 rows per page: skip the first 40, return the next 20
SELECT order_id, order_date
FROM orders
ORDER BY order_date DESC
LIMIT 20 OFFSET 40;

The trap: OFFSET still has to scan and discard every skipped row internally before it can return the requested page. OFFSET 1000000 forces the database to process a million rows just to throw them away and hand you the next 20 — real, measurable cost on a large, deeply-paginated table.

LIMIT vs TOP vs FETCH FIRST

The concept of capping rows exists everywhere; the keyword and its position in the query differ by system.

The standard SQL:2008 syntax, FETCH FIRST n ROWS ONLY, works on PostgreSQL too and is the most portable choice if a codebase genuinely needs to run against multiple database systems. For everyday work on Postgres, MySQL, or BigQuery, LIMIT is simpler and universally understood.

Mistakes That Give Away a Beginner

• Using LIMIT without ORDER BY for a "top N" query — the exact bug from this article's opening. LIMIT alone gives you an arbitrary N rows, not the top N.
• Forgetting DESC on a "most/highest/latest" query. ASC is the silent default — a query that runs fine but returns the smallest values when you meant the largest.
• Relying on the default NULL sort position without checking it for the specific database in use, especially on reports where missing data shouldn't dominate the top of the list.
• Deep OFFSET pagination on a large, frequently-paged table — works fine in testing on 500 rows, then gets measurably slower once the table and the page number both grow.
• Sorting by column position (ORDER BY 2) in code meant to last — it silently breaks the moment someone reorders the SELECT list.

Practice: Write These Three Queries Yourself

Using the same orders table (order_id, customer_id, product_name, price, quantity, order_date, status), try writing these:

Frequently Asked Questions

Conclusion: Sort First, Then Trim

ORDER BY and LIMIT are simple individually and easy to misuse together. The core rule to keep: LIMIT is only as meaningful as the ORDER BY behind it — without a real sort, LIMIT just samples arbitrary rows that happen to satisfy WHERE. Name your sort direction explicitly, treat NULL ordering as something to set deliberately rather than assume, and reach for keyset pagination instead of a growing OFFSET once you're paging deep into a large table.

Practice the three queries above against a real table, then move to the next article in this cluster, which covers SQL data types — what every column you're sorting and filtering is actually storing under the hood.