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

Here is the orders table we will use. Each row represents one order, and each order_id is unique.

The table is displayed in order_id order to make it easy to read. That display order does not tell SQL how to order a query result. Consider this query:

SELECT
    order_id,
    customer,
    product
FROM orders;

It returns the three selected columns for every order, but it does not request any particular order for the rows. You might see order 1001 first and order 1006 last when you run it. Even if that happens several times, you cannot rely on the next run showing the rows in the same order. The database can read and return the rows differently as its data or the way it runs the query changes.

If the requirement says that rows must appear in a particular order, we can add ORDER BY at the end of our SQL query. For example, to show the orders by customer name:

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

One possible result is:

The customer names now determine the order: Asha comes before Ben, followed by Carla and Dev. However, Asha has two orders, and Ben has two orders. ORDER BY customer does not decide which of Asha’s orders comes first, or which of Ben’s comes first. We will add that rule after we learn how to choose the sort direction.

ASC means ascending order. For numbers, it puts smaller values before larger values. Suppose we want to compare orders from the lowest unit price to the highest:

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

One possible result is:

The prices increase as we move down the result. ASC is the default direction, so ORDER BY unit_price and ORDER BY unit_price ASC mean the same thing. Writing ASC can still make the query easier to read, particularly when we sort by several columns.

For the customer names in our example, ascending order gives Asha, Ben, Carla, and Dev.

To put larger numbers first, use DESC, which means descending order:

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

One possible result is:

The requirement usually tells us which direction to choose. “Cheapest unit prices first” calls for ORDER BY unit_price ASC; “most expensive unit prices first” calls for ORDER BY unit_price DESC.

Notice that orders 1001 and 1005 both have a unit price of 750. Either order can appear first within that pair in both queries. Choosing ASC or DESC decides how different prices compare; it does not decide how rows with equal prices compare.

Return to the customer-name example. Suppose the requirement is:

The requirement gives us two rules, so we put both in ORDER BY:

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

Read the columns in ORDER BY from left to right. customer ASC determines the order when the customer names differ. When the names are equal, order_id ASC determines the order instead. Asha’s 1001 therefore comes before her 1005, and Ben’s 1002 comes before his 1006.

The second column is called a tie-breaker because it gives SQL a rule for rows that are equal on the first column. It does not sort all orders by ID independently of the customer names. It is used only when the earlier sort value ties.

Each column also has its own direction. If the requirement changes to show the larger order ID first within each customer, keep customer ASC and change only order_id to DESC:

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

The customers remain in the same alphabetical order, but Asha’s and Ben’s orders switch places. DESC applies to order_id here; it does not make customer descending as well.

So far, we have sorted by columns already stored in orders. We can also sort by a calculation. To find the total amount for each order, multiply quantity by unit_price. For example, order 1001 contains two wireless mice at 750 each, so its amount is 2 * 750 = 1500. Order 1003 contains three laptop sleeves at 1200 each, so its amount is 3 * 1200 = 3600.

We can name this calculation with AS order_amount and use that name in ORDER BY:

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

One possible result is:

order_amount is an alias, meaning a name for the calculated value in this query. It does not add a stored column to orders. It lets us write ORDER BY order_amount DESC instead of repeating quantity * unit_price in the sort clause.

We are comparing all six orders, so Dev’s cancelled order is included and has the highest amount. If a requirement asks for paid orders only, add WHERE status = 'paid' before ORDER BY, using the filtering rules from P01.

There is also a tie: Carla’s order 1003 and Ben’s order 1006 both have an amount of 3600. The query puts both below Dev’s 8500, but either 3600 row can appear first. That detail becomes more important when we return only part of the result.

Suppose we want the three orders with the highest amounts. We can use LIMIT to return only those rows. Place it after ORDER BY, as shown below:

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

One possible result is:

ORDER BY order_amount DESC sorts orders from highest to lowest amount. LIMIT 3 returns the first three rows. Both orders worth 3600 fall within this limit, so both are included. However, their order is not guaranteed.

LIMIT 3 returns at most three rows, not always exactly three. If only two rows remain after filtering, the query returns those two rows.

Why LIMIT needs an ordering for a top result

Now consider a query with LIMIT 3 but no ORDER BY:

SELECT
    order_id,
    customer
FROM orders
LIMIT 3;

This asks for at most three rows, without saying which three should be chosen. It does not request the oldest orders, the highest-value orders, or the lowest order IDs. Those are different requirements, and each needs an ordering that expresses it.

If we want the three lowest order IDs, the complete query is:

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

Adding the unique order_id as the final sorting column makes the result predictable. It determines which three rows are selected and their order. This is the basic idea of a top-N query: sort the data by the required priority, then return at most N rows.

The equivalent syntax in SQL Server

SQL Server uses TOP for the examples shown here. TOP (3) goes directly after SELECT:

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

This returns the same three orders as the highest-value LIMIT 3 example, with the same unresolved order between the two 3600 rows. Although TOP (3) appears near the beginning of the query, ORDER BY still determines which rows belong at the top. Without ORDER BY, TOP (3) also leaves the choice of rows unspecified.

The three-row result included both orders worth 3600. What changes if we ask for only two rows?

Dev’s order 1004, worth 8500, must still come first. There is now only one place left in the result, but Carla’s order 1003 and Ben’s order 1006 both have the next-largest amount. Before reading the explanation, predict what SQL is allowed to return.

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

This is a question about the requirement, not a SQL error. If two orders tie for the last position, should we select just one or include both? The next two sections explain how to handle each case.

Suppose the requirement says:

We already know how to express both sorting rules. Put order_amount DESC first, followed by order_id ASC, and keep LIMIT 2:

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

order_amount DESC puts Dev’s 8500 first. When SQL compares the two amounts of 3600, it uses order_id ASC. Order 1003 comes before order 1006, so Carla’s order takes the second place and Ben’s order falls outside the limit.

No two rows in this table have the same order_id. The complete ORDER BY therefore gives every row a clear position, even when amounts tie. This is called a deterministic result, for the same input rows and the same query, the ordering rules determine the same result.

Set Tie-Breaking Rules

The smaller order ID is appropriate here because the requirement asks for it. Another requirement might say:

To meet that requirement, we need a value that records when the order was placed. A larger order ID does not automatically mean a newer order. Suppose an extended version of orders also has an ordered_at column that stores the order date and time; that column is not part of the introductory table. We could then write:

-- This example requires an ordered_at column.
SELECT
    order_id,
    customer,
    ordered_at,
    quantity * unit_price AS order_amount
FROM orders
ORDER BY
    order_amount DESC,
    ordered_at DESC,
    order_id ASC
LIMIT 2;

The amount remains our first priority. When amounts are equal, ordered_at DESC places the newer order first. If the timestamps also match, order_id ASC decides the final order.

Here, we assume every order has an ordered_at value. If timestamps can be NULL, we must also decide where those rows belong.

The same approach works with SQL Server’s TOP. Use the row limit to control how many rows are returned, and ORDER BY to define their priority and break ties.

Now change the requirement:

This rule allows more than two rows. Dev’s 8500 is first, and the second row is 3600. Because both Carla’s and Ben’s orders have that amount, both belong in the result.

SQL Server provides TOP (...) WITH TIES for this requirement:

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

One possible result is:

TOP (2) establishes the two-row cutoff, and WITH TIES includes any additional rows equal to the last selected row on the ORDER BY values. Here, the cutoff amount is 3600, so the result contains three rows. Carla and Ben can still appear in either order within their tie.

The cutoff is based on rows, not on counting different amounts. If the two largest orders both had an amount of 8500, the second row would also have 8500. TOP (2) WITH TIES would then include every order worth 8500; it would not move down to the next different amount.

There is one important difference from the previous section. WITH TIES checks the complete set of expressions in ORDER BY. If we changed this query to ORDER BY order_amount DESC, order_id ASC, two different orders could no longer tie on both values because order_id is unique. For our table, that change would return only two rows. To include every order tied on amount, keep the tie definition based on order_amount.

TOP (2) WITH TIES is SQL Server syntax. PostgreSQL supports FETCH FIRST 2 ROWS WITH TIES after ORDER BY. It returns the first two rows and any additional rows tied with the second row. DuckDB does not support this syntax directly. Instead, use QUALIFY RANK() OVER (ORDER BY order_amount DESC) <= 2 to include ties at the cutoff. Use LIMIT 2 when you need at most two rows, and the tie-aware approach when all rows sharing the cutoff value must be included.

So far, every value we have sorted has been given. As we learned in P02, a column can also contain NULL. A sorting requirement then needs to answer another question: should rows with missing values come before or after rows with given values?

For this example, consider an extended orders table with the following discount_amount values. This column is additional to the columns shown in the introductory table:

Suppose we want given discounts from smallest to largest, followed by orders whose discount amount is missing. PostgreSQL and DuckDB support NULLS LAST to express that placement:

-- PostgreSQL or DuckDB; requires the discount_amount column above.
SELECT
    order_id,
    discount_amount
FROM orders
ORDER BY
    discount_amount ASC NULLS LAST,
    order_id ASC;

discount_amount ASC puts 150, 200, and 300 in increasing order. NULLS LAST places the missing amounts after those values. Finally, order_id ASC orders the three rows whose discount amounts are all NULL.

If the requirement instead puts missing amounts first and given amounts from largest to smallest, use DESC NULLS FIRST:

-- PostgreSQL or DuckDB; requires the discount_amount column above.
SELECT
    order_id,
    discount_amount
FROM orders
ORDER BY
    discount_amount DESC NULLS FIRST,
    order_id ASC;

The direction and the position of NULL are separate choices. ASC or DESC orders the given values, while NULLS FIRST or NULLS LAST says where the missing values belong. Neither clause replaces NULL with zero or changes the stored data.

Default NULL placement differs between databases. For example, PostgreSQL and SQL Server do not use the same default for ORDER BY discount_amount ASC. SQL Server also does not support the NULLS FIRST and NULLS LAST clauses shown here.

Prefer column names to positions in ORDER BY

Some databases let us refer to a selected column by its position. In the following query, 3 means the third expression in SELECT, which is unit_price:

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

It sorts by descending unit price, just like the earlier example. But if someone moves customer into the third position later, ORDER BY 3 DESC will sort by customer instead. The query may still run while giving a different order than intended.

Writing ORDER BY unit_price DESC makes the rule clear without counting columns. For calculations, an alias such as ORDER BY order_amount DESC serves the same purpose.

We can now build an ordered result by answering three questions together: which values have the highest priority, what happens when those values tie, and how many rows we need.

For example, suppose the requirement is:

The largest amounts come first, so we need order_amount DESC. Equal amounts use the smaller ID first, so we add order_id ASC. The result needs at most five rows, so we finish with LIMIT 5:

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

The two orders worth 3600 both fit in this result, and their IDs determine their order. Order 1005, worth 750, comes sixth and is left out by LIMIT 5.

For this question, use a simplified orders table containing only order_id and order_amount. The amounts are the same as the calculated amounts in our earlier examples, but here order_amount is available directly as a column:

The requirement is to return exactly two orders with the highest amounts; if amounts tie, return the smaller order ID first.

-- 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;

When reviewing a query that asks for the “highest,” “lowest,” “latest,” or “earliest” rows, start with its ORDER BY. Check that the first sort value represents the requirement and that equal values have the required treatment. Then check the row limit. A query can run successfully and still choose the wrong rows if one of these rules is missing.

ORDER BY defines the order of the result. LIMIT or TOP controls how much of that result is returned. Putting both rules into the query, including the required treatment of ties and missing values, makes the result match the question we are trying to answer.