SQL ORDER BY and LIMIT: sorting and returning top results correctly

Query results have no guaranteed order

A table may contain rows in a way that appears naturally ordered. For example, consider the following orders table:

If we run SELECT order_id, customer, product FROM orders; the result might appear in order_id order. It is tempting to assume that SQL will always return the rows this way. It will not.

Unless a query explicitly uses ORDER BY, the order of returned rows is not guaranteed. A database may return rows differently depending on its execution plan, indexes, storage layout, or other implementation details.

Sorting rows with ORDER BY

The ORDER BY clause determines how rows should be arranged in the result. Suppose we want orders sorted alphabetically by customer:

SELECT
    order_id,
    customer,
    product
FROM orders
ORDER BY customer;

A useful way to read this is: return the orders, then arrange the result by customer.

ASC: ascending order

ASC means ascending. For numbers, this normally means smaller values first.

SELECT
    order_id,
    customer,
    unit_price
FROM orders
ORDER BY unit_price ASC;

The values increase from the lowest price to the highest. For text, ascending order usually corresponds to the ordering rules defined by the database’s collation. ASC is the default direction, so ORDER BY unit_price and ORDER BY unit_price ASC express the same ordering. Even though ASC can be omitted, writing it explicitly can sometimes make a multi-column sort easier to read.

DESC: descending order

DESC reverses the direction. Suppose we want the most expensive unit prices first:

SELECT
    order_id,
    customer,
    unit_price
FROM orders
ORDER BY unit_price DESC;

ASC  → lower to higher
DESC → higher to lower

For business questions, the direction usually comes directly from the wording. “Cheapest products first” suggests ORDER BY unit_price ASC, while “most expensive products first” suggests ORDER BY unit_price DESC.

Sorting by more than one column

What happens when several rows have the same value in the first sort column? ORDER BY customer tells SQL how Asha should compare with Ben. It does not tell SQL how Asha’s two rows should be ordered relative to each other. We can provide a second sort condition:

SELECT
    order_id,
    customer
FROM orders
ORDER BY
    customer ASC,
    order_id ASC;

SQL first sorts by customer, and when two rows have the same customer, it sorts those rows by order_id. Official PostgreSQL documentation describes later ORDER BY expressions as the values used to sort rows that are equal according to earlier expressions. You can also mix directions:

ORDER BY
    customer ASC,
    order_id DESC;

This means: sort customers alphabetically, and within each customer, put the larger order_id first. Each sort column has its own direction.

Sorting calculated values

ORDER BY is not limited to values stored directly in the table. Recall that the total amount of an order can be calculated as quantity * unit_price. We can give this value an alias and then sort by it:

SELECT
    order_id,
    customer,
    quantity * unit_price AS order_amount
FROM orders
ORDER BY order_amount DESC;

Notice that orders 1003 and 1006 both have an amount of 3600. We will return to that tie shortly.

Limiting the number of rows with LIMIT

Sometimes we do not need every row in the result. Suppose we want only the three highest-value orders. Using SQL dialects that support LIMIT, we can write:

SELECT
    order_id,
    customer,
    quantity * unit_price AS order_amount
FROM orders
ORDER BY order_amount DESC
LIMIT 3;

ORDER BY determines the sequence. LIMIT 3 then restricts the result to at most three rows. PostgreSQL documents LIMIT as returning only a portion of the rows produced by the rest of the query. This combination is commonly used for questions such as top 10 highest-value orders, five cheapest products, ten largest transactions, or three lowest scores.

LIMIT without ORDER BY does not mean Top-N

Consider SELECT order_id, customer FROM orders LIMIT 3;. This means “return at most three rows.” It does not mean “return the first three orders,” nor “the oldest three,” nor “the three lowest order IDs.” There is no requested ordering, so the database is free to return any qualifying subset. PostgreSQL explicitly recommends using ORDER BY with LIMIT when a predictable subset matters.

SQL Server: using TOP

SQL Server commonly uses TOP instead of the LIMIT syntax shown above. The equivalent SQL Server query for the three highest-value orders is:

SELECT TOP (3)
    order_id,
    customer,
    quantity * unit_price AS order_amount
FROM orders
ORDER BY order_amount DESC;

SQL Server places TOP directly after SELECT, while ORDER BY still determines which rows count as the top rows. Microsoft recommends using ORDER BY with TOP when you need to predictably identify which rows TOP affects. So the ideas are the same even though the syntax differs: LIMIT 3 or TOP (3) restricts the number of returned rows, and ORDER BY determines which rows belong at the beginning of that result.

The Top-N query that is not deterministic

Consider this requirement: return the two highest-value orders. We write the query below. At first glance, this looks completely correct. Let us examine the sorted values:

The highest value is clear: 1004 | Dev | 8500. But the next highest amount is 3600, and two orders have that value: 1003 | Carla | 3600 and 1006 | Ben | 3600. The query says ORDER BY order_amount DESC, but it gives SQL no rule for deciding which 3600 row should come first. Then LIMIT 2 cuts the result after the second row.

SELECT
    order_id,
    customer,
    quantity * unit_price AS order_amount
FROM orders
ORDER BY order_amount DESC
LIMIT 2;

Add a tie-breaker

To make a Top-N query deterministic, define what should happen when the main sort values tie. Suppose our rule is: higher order amounts first, and if two orders have the same amount, use the smaller order_id first. We can write:

SELECT
    order_id,
    customer,
    quantity * unit_price AS order_amount
FROM orders
ORDER BY
    order_amount DESC,
    order_id ASC
LIMIT 2;

The two 3600 rows no longer tie because order_id resolves the tie. Since order_id uniquely identifies an order, the complete ordering is unique. This is a deterministic Top-N query. The same principle applies whether you use LIMIT, TOP, or another row-limiting syntax.

The tie-breaker should come from the requirement

Do not automatically add ORDER BY amount DESC, order_id ASC to every Top-N query simply because order_id is available. Ask what the business actually wants. Suppose the requirement is “return the two largest orders; if values tie, prefer the newest order.” Then the correct secondary sort should represent recency, and if ordered_at can also tie, you may still need a final unique column:

ORDER BY
    order_amount DESC,
    ordered_at DESC,
    order_id ASC

Top-N with ties is a different requirement

Sometimes you do not want to break a tie. Consider “return the two highest order amounts, but include every order tied at the cutoff.” The second-highest amount is 3600, and two orders share it. If the business requirement says both tied orders should be included, the correct result contains three rows even though the requested rank boundary is two. Some database systems provide syntax for this behaviour; for example, SQL Server supports TOP (...) WITH TIES when used with ORDER BY.

SELECT TOP (2) WITH TIES
    order_id,
    customer,
    quantity * unit_price AS order_amount
FROM orders
ORDER BY order_amount DESC;

The important distinction is between two different requirements: “return exactly two rows” and “return the top two values, including all ties.” Those are not always the same problem.

Sorting NULL values, and two more traps

The previous article introduced NULL. Sorting nullable columns introduces another question: should missing values appear first or last? Suppose discount_amount contains 150, NULL, 300, NULL, NULL, 200. A query such as ORDER BY discount_amount ASC does not behave identically across every SQL database. For example, PostgreSQL and SQL Server have different default placement rules for NULL values. PostgreSQL also supports explicit NULLS FIRST and NULLS LAST syntax:

ORDER BY discount_amount ASC NULLS LAST;
ORDER BY discount_amount DESC NULLS FIRST;

Sorting one column does not order ties

Consider ORDER BY status ASC. Suppose several orders have status = paid. SQL guarantees where the paid group appears relative to other statuses according to the database’s text ordering. It does not guarantee the order of rows inside that group. If that matters, add another sort, such as ORDER BY status ASC, order_id ASC. This idea becomes especially important when the result is limited, paginated, exported repeatedly, compared between runs, or used by another application. The more stable the output needs to be, the more important a complete ordering becomes.

ORDER BY position numbers are hard to maintain

Some databases allow queries such as ORDER BY 3 DESC, where 3 means the third selected column. The query may be valid, but it is less clear than ORDER BY unit_price DESC. If someone changes the column order later, the meaning of ORDER BY 3 can also change. Microsoft’s SQL Server documentation specifically recommends avoiding positional integers for this reason. For readable production SQL, prefer column names or meaningful aliases.

How to spot sorting and limiting problems

Look for these signs:

When you see one of these, inspect the complete ORDER BY clause.

Break an ordered requirement into three questions

SELECT
    order_id,
    customer,
    quantity * unit_price AS order_amount
FROM orders
ORDER BY
    order_amount DESC,
    order_id ASC
LIMIT 5;

One more prediction

Consider these order amounts:

Requirement: return exactly two orders with the highest amounts; if amounts tie, return the smaller order ID first. Which query correctly expresses the requirement?

-- Option A
SELECT order_id, order_amount FROM orders
LIMIT 2;

-- Option B
SELECT order_id, order_amount FROM orders
ORDER BY order_amount DESC
LIMIT 2;

-- Option C
SELECT order_id, order_amount FROM orders
ORDER BY
    order_amount DESC,
    order_id ASC
LIMIT 2;

-- Option D
SELECT order_id, order_amount FROM orders
ORDER BY order_id ASC
LIMIT 2;

Lock these in

Summary

ORDER BY controls the order of rows: ORDER BY unit_price ASC puts lower values first, and ORDER BY unit_price DESC puts higher values first. When the first sort column contains ties, additional columns can resolve them. LIMIT restricts the number of rows in databases that support that syntax, and SQL Server commonly uses TOP (3) for the same general purpose. But limiting rows is meaningful only after you have defined which rows should come first.

Next, we will move from individual rows to summarising many rows together using GROUP BY, COUNT, SUM, AVG, MIN, MAX, and HAVING.