SQL ORDER BY and LIMIT: Sorting and Paging Results

SQL ORDER BY and LIMIT give you control over the order and size of your query results. You wrote a perfectly valid SELECT statement, hit run, and got back hundreds of rows in no particular order.

You Finally Ran Your First Query — Now the Results Are a Mess Sound familiar? Raw database output is like a shuffled deck of cards — technically complete, but useless until you put it in order.

That is where ORDER BY and LIMIT come in. These two clauses give you control over what you see and how much of it you see. Whether you are building a "Top 10" leaderboard, paginating an API response, or simply making sense of your data, sorting and limiting are skills you will use in virtually every SQL query you write.

Table of Contents

1. How ORDER BY Works
2. Sorting by Multiple Columns
3. Limiting Results with LIMIT
4. Pagination with LIMIT and OFFSET
5. Top-N Queries and Real-World Patterns
6. Quick Comparison / Cheat Sheet
7. Common Mistakes to Avoid
8. Practice Makes Perfect
9. Summary
10. FAQs

How ORDER BY Works

The ORDER BY clause sorts your result set by one or more columns. By default, it sorts in ascending order (ASC). Add DESC to reverse the direction.

Syntax:

SELECT columns
FROM table
ORDER BY column [ASC | DESC];

Let's start with a simple example. Find the 10 longest tracks in the Chinook database, sorted from longest to shortest:

SELECT Name, Milliseconds
FROM Track
ORDER BY Milliseconds DESC
LIMIT 10;

Notice how DESC gives us the longest tracks first. Without it, we would get the shortest tracks instead.

Tip: ORDER BY is always one of the last clauses processed — it runs after WHERE, GROUP BY, and HAVING. Only LIMIT and OFFSET come after it.

Sorting by Multiple Columns

Real-world queries often need to sort by more than one column. SQL evaluates each column in the order you list them — the second column only breaks ties in the first.

SELECT c.FirstName, c.LastName, i.Total
FROM Invoice i
JOIN Customer c ON c.CustomerId = i.CustomerId
ORDER BY i.Total DESC, c.LastName ASC
LIMIT 10;

Here, invoices are sorted by total (highest first). When two invoices share the same total, last names break the tie alphabetically.

If you have used GROUP BY and HAVING to aggregate data, you can sort those aggregated results with ORDER BY — for example, sorting groups by their SUM() or COUNT().

Limiting Results with LIMIT

LIMIT restricts the number of rows returned. It is essential for performance and usability — you rarely want to dump an entire table to the screen.

SELECT Name, Composer, Milliseconds
FROM Track
WHERE Composer IS NOT NULL
ORDER BY Milliseconds ASC
LIMIT 5;

Without LIMIT 5, this query would return every track that has a composer — thousands of rows. Adding LIMIT keeps it manageable.

How LIMIT differs across databases:

• MySQL, PostgreSQL, SQLite: LIMIT n
• SQL Server: SELECT TOP n ...
• Oracle: FETCH FIRST n ROWS ONLY (or ROWNUM in older versions)

The concept is the same everywhere; only the syntax differs.

Pagination with LIMIT and OFFSET

When you build a paginated list — like page 2 of search results — you combine LIMIT with OFFSET. The OFFSET tells the database how many rows to skip before starting to return results.

-- Page 1: first 5 customers
SELECT CustomerId, FirstName, LastName, Country
FROM Customer
ORDER BY LastName ASC
LIMIT 5 OFFSET 0;

-- Page 2: next 5 customers
SELECT CustomerId, FirstName, LastName, Country
FROM Customer
ORDER BY LastName ASC
LIMIT 5 OFFSET 5;

The formula is straightforward:

OFFSET = (page_number - 1) * page_size

Performance warning: Large OFFSET values can be slow because the database still reads and discards all the skipped rows. For high-performance pagination, consider keyset pagination (also called cursor-based pagination), where you filter using a WHERE clause on the last seen value instead of using OFFSET.

Top-N Queries and Real-World Patterns

Combining ORDER BY and LIMIT unlocks some of the most common SQL patterns.

Top-N: Best Customers by Revenue

SELECT c.FirstName || ' ' || c.LastName AS Customer, 
       SUM(i.Total) AS TotalSpent
FROM Customer c
JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId, c.FirstName, c.LastName
ORDER BY TotalSpent DESC
LIMIT 5;

This pattern — aggregate, sort, limit — is something you will use constantly for dashboards and reports. If you want to go further with ranking (like handling ties), check out our guide to SQL Window Functions where ROW_NUMBER() and RANK() give you even finer control.

Bottom-N: Least Popular Genres

SELECT g.Name AS Genre, COUNT(t.TrackId) AS TrackCount
FROM Genre g
LEFT JOIN Track t ON g.GenreId = t.GenreId
GROUP BY g.GenreId, g.Name
ORDER BY TrackCount ASC
LIMIT 5;

Flipping DESC to ASC turns a "Top-N" into a "Bottom-N" query. The LEFT JOIN ensures genres with zero tracks still appear.

Latest Records

SELECT InvoiceId, InvoiceDate, Total
FROM Invoice
ORDER BY InvoiceDate DESC
LIMIT 5;

Sorting by a date column in descending order is the go-to pattern for "most recent" queries.

Quick Comparison / Cheat Sheet

Common Mistakes to Avoid

1. Using ORDER BY Without LIMIT in Subqueries

Most databases ignore ORDER BY inside a subquery unless it is paired with LIMIT. The outer query determines the final sort order. If you need sorted subquery results, consider using window functions or a CTE instead.

2. Relying on Default Row Order

Without an explicit ORDER BY, SQL databases return rows in no guaranteed order. The order might look consistent during development but can change with data growth, index changes, or parallel execution. Always add ORDER BY when order matters.

3. Using OFFSET for Deep Pagination

Paginating with OFFSET 100000 forces the database to scan and discard 100,000 rows before returning your page. For large datasets, switch to keyset pagination:

-- Instead of: LIMIT 10 OFFSET 100000
-- Use keyset pagination:
SELECT InvoiceId, InvoiceDate, Total
FROM Invoice
WHERE InvoiceId > 412  -- last ID from previous page
ORDER BY InvoiceId ASC
LIMIT 10;

4. Forgetting That NULL Sorts Differently Across Databases

In PostgreSQL, NULL values sort last in ascending order and first in descending order. In MySQL, NULL is treated as the lowest value. Use NULLS FIRST or NULLS LAST (where supported) to control this explicitly.

5. Sorting by Column Position Instead of Name

While ORDER BY 1 (sort by the first column) works, it makes queries fragile. If someone reorders the columns in the SELECT list, the sort silently changes. Always use column names or aliases for clarity.

Practice Makes Perfect

Reading about ORDER BY and LIMIT only gets you so far. The real learning happens when you write the queries yourself. Try these exercises on SQiLs to cement your skills:

Over 500 learners have already sharpened their SQL skills on SQiLs. Start practicing now and join them.

Summary

ORDER BY and LIMIT are two of the most frequently used SQL clauses — and for good reason. Together they let you:

• Sort results in ascending or descending order by any column (or multiple columns)
• Restrict output to a manageable number of rows
• Paginate through large result sets with OFFSET
• Build Top-N and Bottom-N queries for dashboards and reports

The key takeaway: never assume your database will return rows in a predictable order. If order matters, spell it out with ORDER BY.

Ready to put these skills into practice? Try the sorting exercises on SQiLs — they use real databases and give you instant feedback on your queries.

FAQs

Can I use ORDER BY with GROUP BY?

Yes. ORDER BY runs after GROUP BY and HAVING, so you can sort aggregated results. For example, GROUP BY Country then ORDER BY COUNT(*) DESC gives you countries ranked by row count. See our GROUP BY and HAVING guide for more details.

What is the difference between LIMIT and TOP?

They do the same thing — restrict the number of rows returned. LIMIT is used in MySQL, PostgreSQL, and SQLite. TOP is SQL Server syntax (SELECT TOP 10 ...). Oracle uses FETCH FIRST n ROWS ONLY. The logic is identical across all databases.

Is OFFSET the only way to paginate?

No. OFFSET-based pagination is simple but can be slow for large offsets. Keyset pagination (also called cursor-based pagination) uses a WHERE clause on the last value from the previous page, which is much faster because it leverages indexes. Use OFFSET for small datasets and keyset pagination for production applications at scale.

Does ORDER BY slow down my query?

It can. Sorting requires the database to arrange all qualifying rows before returning results. If you sort by an indexed column, the database can often skip the sort entirely. For unindexed columns or complex expressions, sorting large result sets will add overhead. Combining ORDER BY with LIMIT helps because the database can use a partial sort (top-N heap) instead of sorting everything.