SQL doesn't sort or trim your results unless you tell it to. Learn how ORDER BY, LIMIT, OFFSET, and TOP give you precise control over what your queries return — and in what order — across MySQL, PostgreSQL, and SQL Server.
Imagine you're a data analyst at a retail company. Your manager asks for a simple report: "Show me the top 10 best-selling products this month." You write a query that pulls from the sales table — and your database returns 47,000 rows, unsorted, starting from whatever row happened to be physically first on disk. That's not a report. That's chaos.
This is one of the first real-world problems every SQL learner bumps into. Databases don't have an opinion about which rows matter most or what order makes sense to a human. Unless you tell SQL exactly how to sort and slice your results, you get raw, unordered data in whatever sequence the database engine feels like returning it. The good news is that SQL gives you precise, powerful tools to control exactly this: ORDER BY, LIMIT, OFFSET, and TOP.
By the end of this lesson, you'll know how to sort query results in any direction you choose, restrict results to only the rows you actually need, build pagination for large datasets, and write these clauses correctly across different database systems (MySQL, PostgreSQL, SQLite, and SQL Server all have minor differences worth knowing).
What you'll learn:
ORDER BY works and how to sort by multiple columns in different directionsLIMIT restricts the number of rows returned and why this matters for performanceOFFSET enables pagination through large result setsTOP clause differs from LIMIT and when to use itYou should already be comfortable writing basic SELECT queries with FROM and WHERE clauses. If you're brand new to SQL, read SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries before continuing. You don't need to know anything about joins, aggregation, or subqueries yet — we'll keep examples self-contained.
The ORDER BY clause tells SQL how to rank the rows in your result set before returning them to you. You place it near the end of your query, after FROM and WHERE, like this:
SELECT product_name, unit_price, stock_quantity
FROM products
ORDER BY unit_price;
This returns all products sorted by price from lowest to highest. That direction — lowest to highest — is the default, and it's called ascending order, abbreviated ASC. You can make it explicit if you want:
SELECT product_name, unit_price, stock_quantity
FROM products
ORDER BY unit_price ASC;
To flip it — highest to lowest — use DESC (descending):
SELECT product_name, unit_price, stock_quantity
FROM products
ORDER BY unit_price DESC;
Now your most expensive products appear first. This is what you actually want when a manager asks "show me our most expensive products."
Key insight
SQL makes no guarantees about row order unless you explicitly use ORDER BY. Two identical queries run at different times can return rows in different sequences if you leave out ORDER BY. Never assume "the database will return them in the order I inserted them" — that assumption will burn you eventually.
You can sort by more than one column by listing them separated by commas. SQL applies the sorts left to right: it sorts by the first column, then breaks ties using the second column, and so on.
Consider a customer orders table. You want to sort by customer ID, and for each customer, sort their orders from most recent to oldest:
SELECT customer_id, order_date, order_total
FROM orders
ORDER BY customer_id ASC, order_date DESC;
Here, all of Customer 101's orders appear together (sorted ascending by customer ID), and within that group, the newest orders appear first (descending by date). Each column can have its own direction — you're not locked into one direction for the whole query.
SQL also lets you reference columns by their position in the SELECT list:
SELECT product_name, unit_price, stock_quantity
FROM products
ORDER BY 2 DESC;
The 2 refers to unit_price, the second column selected. You'll see this in old code and quick ad-hoc queries. It works, but it's fragile — if you reorder your SELECT columns later, the sort breaks silently. Stick to column names in production code.
Text sorting is alphabetical by default. ORDER BY last_name sorts A before Z. ORDER BY last_name DESC sorts Z before A. Watch out for case sensitivity — some database configurations treat uppercase and lowercase differently during sorting. Most modern databases default to case-insensitive sorting for ASCII characters, but this can vary.
LIMIT is your most direct tool for controlling how many rows a query returns. It's supported by MySQL, PostgreSQL, and SQLite.
SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 10;
This returns exactly the 10 most expensive products. Notice that ORDER BY and LIMIT almost always work together — limiting without sorting gives you an arbitrary 10 rows, which is rarely useful.
Warning
Always pair LIMIT with ORDER BY unless you genuinely don't care which rows you get. LIMIT without ORDER BY returns whatever the database engine decides to return first, which is implementation-dependent and can change between queries.
LIMIT isn't just about tidying up your results. When you're working with large tables, adding LIMIT can dramatically reduce how much work the database has to do. If you're exploring a new table to understand its structure, running:
SELECT *
FROM events
LIMIT 5;
...is much kinder to your database than pulling all 10 million event records. Think of it as a safety valve during development.
LIMIT applies after WHERE has already filtered rows. The database first finds all rows matching your WHERE condition, then hands LIMIT a set to trim:
SELECT order_id, customer_id, order_total
FROM orders
WHERE order_total > 500
ORDER BY order_total DESC
LIMIT 20;
This finds all orders over $500, sorts them by total descending, and returns only the top 20. The LIMIT is not applied before the WHERE filter — the order of operations matters.
OFFSET lets you skip a specified number of rows before SQL starts returning results. Combined with LIMIT, it's the standard way to implement pagination — breaking a large result set into pages.
Think of a website that shows 25 products per page. Page 1 shows products 1–25, page 2 shows products 26–50, and so on.
Page 1 (rows 1–25):
SELECT product_name, unit_price
FROM products
ORDER BY product_name ASC
LIMIT 25 OFFSET 0;
Page 2 (rows 26–50):
SELECT product_name, unit_price
FROM products
ORDER BY product_name ASC
LIMIT 25 OFFSET 25;
Page 3 (rows 51–75):
SELECT product_name, unit_price
FROM products
ORDER BY product_name ASC
LIMIT 25 OFFSET 50;
The formula is simple: OFFSET = (page_number - 1) * page_size. Your application calculates this number and passes it into the query.
Tip
In MySQL and SQLite, you can write LIMIT 25, 50 as shorthand for LIMIT 25 OFFSET 50 — but this syntax puts offset first, which many developers find confusing. The LIMIT ... OFFSET ... syntax is more readable and consistent across databases. Prefer it.
Here's something that surprises many beginners: large OFFSET values are slow. When you write OFFSET 10000, the database doesn't magically jump to row 10,001. It retrieves all 10,000 prior rows and discards them, then starts counting out your requested results. For page 400 of a large dataset, this is significant work.
For high-performance pagination on large datasets, there's a technique called keyset pagination (or cursor-based pagination) that avoids this problem by filtering on the last seen row's value instead of using OFFSET. That's an advanced topic beyond this lesson's scope, but it's worth knowing the limitation exists.
Note
For most everyday analytical queries and moderate-sized datasets, OFFSET works perfectly well. The performance concern becomes meaningful when you're paginating through millions of rows in a production application.
If you're working in Microsoft SQL Server or Azure SQL Database, you'll use TOP instead of LIMIT. The syntax is slightly different — TOP goes inside the SELECT clause rather than at the end of the query:
SELECT TOP 10 product_name, unit_price
FROM products
ORDER BY unit_price DESC;
TOP 10 means "return only the first 10 rows." The same rule applies: always pair it with ORDER BY unless you want arbitrary results.
SQL Server's TOP has a useful trick that LIMIT doesn't: you can specify a percentage instead of a fixed count:
SELECT TOP 25 PERCENT product_name, unit_price
FROM products
ORDER BY unit_price DESC;
This returns the most expensive 25% of your product catalog — however many rows that works out to be. This is handy when you don't know the table size in advance but want a proportional slice.
Another SQL Server feature worth knowing: WITH TIES. Suppose you ask for the top 5 salespersons by revenue, and three people are tied for 5th place. Without WITH TIES, SQL Server picks arbitrarily which of the three tied rows to include. With it, all tied rows are included:
SELECT TOP 5 WITH TIES
salesperson_name,
total_revenue
FROM sales_summary
ORDER BY total_revenue DESC;
This might return 7 rows if there are three people tied for 5th. It's a thoughtful feature when fairness or completeness matters more than strict row count.
SQL Server doesn't support the bare LIMIT ... OFFSET syntax. Instead, it uses OFFSET ... FETCH NEXT ... ROWS ONLY, which is actually part of the SQL standard (though MySQL and SQLite use LIMIT/OFFSET instead):
SELECT product_name, unit_price
FROM products
ORDER BY product_name ASC
OFFSET 25 ROWS
FETCH NEXT 25 ROWS ONLY;
This is equivalent to LIMIT 25 OFFSET 25 in PostgreSQL or MySQL. The phrasing is more verbose but arguably more readable — "skip 25 rows, then fetch the next 25."
Warning
In SQL Server, OFFSET ... FETCH requires an ORDER BY clause to be present. You'll get an error if you try to use it without one. This is actually a good constraint — it forces you to define a meaningful sort order for your pagination.
One of the most common errors for SQL beginners is putting clauses in the wrong order. SQL is strict about this. The full order for a query using all these clauses looks like this:
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1 ASC
LIMIT 10
OFFSET 20;
In SQL Server:
SELECT TOP 10 column1, column2
FROM table_name
WHERE condition
ORDER BY column1 ASC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
Think of it as a pipeline: FROM tells SQL where to look, WHERE filters, ORDER BY sorts, and then LIMIT/OFFSET or TOP trims the final result. You can't trim before you sort, and you can't sort before you filter. The clauses express what you want, and SQL figures out execution order internally.
Once you start using GROUP BY and aggregate functions, ORDER BY and LIMIT become even more powerful. You can sort by calculated values:
SELECT
category,
COUNT(*) AS product_count,
AVG(unit_price) AS avg_price
FROM products
GROUP BY category
ORDER BY avg_price DESC
LIMIT 5;
This finds the 5 product categories with the highest average price. Notice that you can ORDER BY an alias defined in the SELECT clause — SQL is smart enough to resolve this in most databases (MySQL, PostgreSQL, SQLite all support it; SQL Server requires more care with aliases in ORDER BY).
You can also order by expressions that don't appear in your SELECT list at all in many databases, though that makes queries harder to read.
Work through these queries using any SQL environment you have access to (DB Fiddle, SQLite Browser, or a local PostgreSQL/MySQL installation all work).
Set up a simple table to practice with:
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
department TEXT,
salary DECIMAL(10,2),
hire_date DATE
);
INSERT INTO employees VALUES
(1, 'Maria', 'Santos', 'Engineering', 95000, '2019-03-15'),
(2, 'James', 'Okafor', 'Marketing', 72000, '2021-07-01'),
(3, 'Priya', 'Mehta', 'Engineering', 105000,'2018-11-20'),
(4, 'David', 'Chen', 'Sales', 68000, '2022-01-10'),
(5, 'Fatima', 'Al-Rashid','Engineering', 112000,'2017-06-03'),
(6, 'Thomas', 'Lindqvist','Marketing', 78000, '2020-09-14'),
(7, 'Aisha', 'Williams', 'Sales', 71000, '2021-03-22'),
(8, 'Henrik', 'Berg', 'Engineering', 98000, '2019-08-30'),
(9, 'Yuki', 'Tanaka', 'Marketing', 81000, '2020-02-11'),
(10,'Carlos', 'Rivera', 'Sales', 75000, '2018-04-05');
Exercise 1: Write a query that returns all employees sorted by salary from highest to lowest. What's the highest-paid employee?
Exercise 2: Return only the top 3 highest-paid employees (name, department, salary).
Exercise 3: Return employees sorted alphabetically by last name, then by first name within the same last name.
Exercise 4: Write a query that simulates "page 2" of employee results, showing 3 employees per page, sorted by hire date ascending.
Exercise 5: Find the 2 departments with the highest average salary. (Hint: use GROUP BY department, ORDER BY, and LIMIT.)
Expected results for Exercise 4 (page 2, 3 per page, sorted by hire_date ASC):
SELECT first_name, last_name, hire_date
FROM employees
ORDER BY hire_date ASC
LIMIT 3 OFFSET 3;
The three employees hired 4th, 5th, and 6th in chronological order should appear. Work it out yourself first, then verify.
-- Don't do this
SELECT * FROM orders LIMIT 10;
You'll get 10 rows, but you have no idea which 10. The database might return them in primary key order today and insertion order tomorrow. Always sort first.
Some beginners think LIMIT 5 means "look at only 5 rows from the table." It doesn't. It means "return 5 rows from the filtered result." All the filtering work still happens first. If you want to limit the rows a database scans for performance reasons, that's a different topic involving indexes — covered in SQL Indexes Explained: How They Work and When to Create Them.
LIMIT doesn't work in SQL Server. TOP doesn't work in PostgreSQL or MySQL. OFFSET ... FETCH isn't supported in older MySQL versions. Know which database you're working with and use the right syntax. Most error messages for this are clear ("syntax error near 'LIMIT'") so they're easy to diagnose.
A very common bug: developers think page 2 with 10 items per page should use OFFSET 10, but they write OFFSET 9 or OFFSET 11. Remember: OFFSET is a skip count, not a start position. If you want to skip the first 10 rows and start at row 11, OFFSET 10 is correct. Use the formula: OFFSET = (page - 1) * page_size.
In most databases, you can order by a column even if it's not in your SELECT list. But in SQL Server, this can cause issues when combined with DISTINCT or certain subquery patterns. If you're getting unexpected errors around ORDER BY, check whether the column you're sorting by is accessible in the query's output scope. If you're working through more complex filtering scenarios, Advanced SQL Filtering and Sorting: Mastering WHERE, ORDER BY, and Query Optimization covers these edge cases in depth.
NULL values sort in a database-specific way. PostgreSQL puts NULLs last in ASC order and first in DESC order by default. MySQL puts NULLs first in ASC order. SQL Server puts NULLs first in ASC order too. If your sorted results have NULLs appearing in a surprising place, this is why. PostgreSQL lets you control it explicitly: ORDER BY column ASC NULLS LAST. For other databases, you can use a workaround with a CASE expression in your ORDER BY. See the lesson on NULL Handling in SQL: IS NULL, COALESCE, and NULLIF for techniques.
You now have solid control over what SQL returns and in what order. Let's recap the key ideas:
ORDER BY column ASC|DESC sorts your result set. Use multiple columns to create nested sort orders. Always include it whenever the order of results matters — which is almost always.LIMIT n (MySQL, PostgreSQL, SQLite) returns only the first n rows after sorting. Pair it with ORDER BY to get meaningful results.OFFSET n skips n rows before returning results. Combined with LIMIT, it enables page-by-page navigation through large datasets.TOP n (SQL Server) does what LIMIT does, but goes at the top of the SELECT clause. Supports PERCENT and WITH TIES.OFFSET ... FETCH NEXT ... ROWS ONLY (SQL Server) is the standard-compliant pagination syntax.Key insight
These clauses are the last things that happen to your result set before it's handed back to you. They work on the output of all your filtering, joining, and grouping — not on the raw table. This mental model helps you predict what they'll do in any query.
From here, a natural next step is understanding how to filter and sort within groups of data using GROUP BY and HAVING. Read Grouping and Summarizing Data: COUNT, SUM, AVG, and GROUP BY for Beginners to see how ORDER BY and LIMIT combine with aggregation to answer questions like "what are my top 5 product categories by revenue?"
As your queries grow more complex — involving joins, subqueries, and conditional logic — the principles here stay exactly the same. ORDER BY and LIMIT always come last, always act on the final result set, and always give you the control you need to surface the rows that actually matter. That's a skill you'll use in every serious SQL query you ever write.