Limiting Results with LIMIT

Getting Just What You Need

Sometimes you don't need every row. You want the top 10 customers, the 5 most recent orders, or just a sample to check your query logic. LIMIT lets you cap how many rows come back.

This returns the 5 highest-paid employees.

Dialect Differences

This is one of the biggest syntax differences across SQL dialects:

• PostgreSQL / MySQL — LIMIT 10 at the end of the query
• SQL Server — SELECT TOP 10 ... immediately after SELECT
• Oracle — FETCH FIRST 10 ROWS ONLY at the end, after ORDER BY

Without ORDER BY, Results Are Unpredictable

You can use row-limiting without ORDER BY, but the rows you get back are arbitrary — it depends on how the database stores the data:

-- PostgreSQL / MySQL
SELECT * FROM techcorp_workforce LIMIT 3;

-- Oracle
SELECT * FROM techcorp_workforce FETCH FIRST 3 ROWS ONLY;

-- SQL Server
SELECT TOP 3 * FROM techcorp_workforce;

All three return 3 rows in no guaranteed order. For consistent results, always pair with ORDER BY.

The Top N Pattern

The most common use of row-limiting is the "Top N" pattern: ORDER BY combined with your dialect's limit clause.

This returns the three most expensive orders.

The same pattern works for any "top N" question — here's 5 most recently hired employees across all three dialects:

-- PostgreSQL / MySQL
SELECT first_name, last_name, joining_date
FROM techcorp_workforce
ORDER BY joining_date DESC
LIMIT 5;

-- Oracle
SELECT first_name, last_name, joining_date
FROM techcorp_workforce
ORDER BY joining_date DESC
FETCH FIRST 5 ROWS ONLY;

-- SQL Server
SELECT TOP 5 first_name, last_name, joining_date
FROM techcorp_workforce
ORDER BY joining_date DESC;

Hour Of Highest Gas Expense

Skipping Rows with OFFSET

OFFSET lets you skip a number of rows before returning results. It's used for pagination, and the syntax splits along the same dialect lines:

-- PostgreSQL / MySQL
SELECT
  first_name,
  last_name,
  salary
FROM techcorp_workforce
ORDER BY salary DESC
LIMIT 5 OFFSET 5;

-- Oracle / SQL Server
SELECT
  first_name,
  last_name,
  salary
FROM techcorp_workforce
ORDER BY salary DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

Both skip the top 5 earners and return employees ranked 6–10. Oracle and SQL Server share the same OFFSET … FETCH NEXT pagination syntax.

Pagination example:

-- PostgreSQL / MySQL
LIMIT 10 OFFSET 0   -- Page 1
LIMIT 10 OFFSET 10  -- Page 2
LIMIT 10 OFFSET 20  -- Page 3

-- Oracle / SQL Server
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY   -- Page 1
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY  -- Page 2
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY  -- Page 3

Putting It All Together

Row limiting works with all the other clauses you've learned. The full structure per dialect:

-- PostgreSQL / MySQL
SELECT columns
FROM table
WHERE conditions
ORDER BY column
LIMIT n;

-- Oracle
SELECT columns
FROM table
WHERE conditions
ORDER BY column
FETCH FIRST n ROWS ONLY;

-- SQL Server
SELECT TOP n columns
FROM table
WHERE conditions
ORDER BY column;

This should return Dresses (200), Boats (100), and Jeans (80).

Key Takeaways

• Row-limiting clauses (LIMIT, TOP, FETCH FIRST) cap the number of rows returned.
• Always pair with ORDER BY for predictable results.
• OFFSET skips rows — useful for pagination, but slow on large tables.
• Syntax varies by dialect: LIMIT (PostgreSQL/MySQL), TOP (SQL Server), FETCH FIRST (Oracle).
• Query position varies: LIMIT/FETCH FIRST go at the end; TOP goes right after SELECT.

What's Next

Congratulations! You've completed the core SQL Foundations module. You now know how to SELECT data, filter with WHERE, use logical operators, match patterns, handle NULLs, sort results, and limit output. Now, join us in the next module where you'll learn aggregations and grouping — the tools that turn raw rows into business insights.