SQL ORDER BY and LIMIT: Sort Results and Find the Top Rows

A table on the screen can look sorted even when your query never requested a sort. That apparent order is not a promise. SQL ORDER BY and LIMIT help you ask for a result you can explain, such as the two largest orders, with a clear rule for ties.

Practice setup: These lessons use SQLite and the fictional shop database from the QueryFun SQL practice lab. Open that lab, run its setup once in a fresh practice database, then return here. Do not run the INSERT statements again for each lesson. You can use SQLite Fiddle without installing a database server.

The customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional currency units. Each order belongs to one customer. A customer can have several orders.

Choose a column and direction

Start with the largest order amount first:

SELECT order_id, amount
FROM orders
ORDER BY amount DESC;

DESC means descending. You get order 104 at 1500, 101 at 1200, 102 at 800 and 103 at 600. ASC means ascending and is the default direction. Changing DESC to ASC puts the smallest amount first.

Sorting the result does not reorganise the stored database or change the amounts. It gives this query a presentation order. If a later query needs the same order, it must ask for it again.

Add a second sort key

The first column after ORDER BY is the primary sort key. Additional columns decide the order within ties. For example, customers can share a city:

SELECT customer_id, name, city
FROM customers
ORDER BY city ASC, customer_id ASC;

Brian appears first because Mombasa comes before Nairobi in this simple data. Within Nairobi, Amina's customer_id 1 comes before Chao's 3. A stable identifier is useful as the last sort key when you need a predictable result.

Each column gets its own direction. ORDER BY city ASC, customer_id DESC keeps the city groups ascending but reverses customer IDs inside each city. DESC at the end does not automatically apply to every earlier column.

Ask for a small number of rows

SQLite uses LIMIT to cap how many rows a query returns:

SELECT order_id, amount
FROM orders
ORDER BY amount DESC, order_id ASC
LIMIT 2;

This returns 104 at 1500 and 101 at 1200. LIMIT 2 means at most two result rows. It does not mean two distinct amounts, and it does not automatically include every row tied at the cutoff.

Our sample has no amount ties, but real data often does. The order_id tie-breaker tells SQLite which tied row comes first. If your requirement is “include everyone tied for second place,” that needs a different approach, such as ranking functions, rather than a plain LIMIT 2.

Filter before choosing the top rows

Suppose you need the largest order placed by customer 1:

SELECT order_id, customer_id, amount
FROM orders
WHERE customer_id = 1
ORDER BY amount DESC, order_id ASC
LIMIT 1;

The answer is order 101, amount 1200. WHERE limits the candidates to customer 1's orders. ORDER BY ranks those candidates, and LIMIT keeps the first one. Remember this sequence when checking a query that seems to return the wrong top result.

Watch for numbers stored as text

Correct sorting depends on the stored data type. Numeric values stored as text can sort by text rules rather than numeric size. The lab defines amount as an integer, so its amounts behave as numbers. If an imported dataset sorts strangely, inspect the column and input values before adding more sorting conditions.

LIMIT syntax varies across database products. This lesson uses SQLite. SQL Server commonly uses TOP or OFFSET/FETCH instead. Keep the idea of sorting and limiting separate from the exact spelling your database requires.

Try it yourself

Return the two smallest orders whose amounts are at least 800. Include order_id and amount. Make the order predictable even if two qualifying orders later have the same amount.

SELECT order_id, amount
FROM orders
WHERE amount >= 800
ORDER BY amount ASC, order_id ASC
LIMIT 2;

Keep learning: Return to the SQL learning path to choose your next lesson.

Further reading: SQLite SELECT documentation.