Previous
Written by: rJTutorial Team | Last Updated:

SQL LIMIT

The LIMIT clause is an essential tool in a SQL developer's toolkit. It allows you to specify exactly how many records you want the database to return. This is incredibly useful when you are dealing with tables containing millions of rows, but you only need a small sample or a specific subset of data for your application's user interface.

Developer Tip: Always use LIMIT when you are exploring a new, massive dataset for the first time. Running SELECT * FROM huge_table without a limit can hang your IDE or put unnecessary load on the production database.

 

Basic Syntax of LIMIT

The LIMIT clause is placed at the very end of your SQL query. It tells the database engine to stop gathering rows once it reaches the specified number.

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;
  • number_of_rows: A positive integer representing the maximum number of records to return.
Common Mistake: Thinking that LIMIT guarantees which specific rows you will get. Without an ORDER BY clause, the database returns the "first" rows it finds, which can change depending on how the data is stored on the disk.

 

Key Features of LIMIT Clause

1. Limiting the Number of Rows

In its simplest form, LIMIT acts as a safety valve or a way to get a quick snapshot of your data. For example, if you just want to see what the data inside the customers table looks like, you can fetch just a few rows.

SELECT name, city
FROM customers
LIMIT 5;

This query will return the first 5 rows the database encounters in the customers table.

2. Combining LIMIT with ORDER BY

To make LIMIT truly useful, you should almost always pair it with ORDER BY. This ensures that your results are deterministic meaning you get the same "top" or "bottom" results every time you run the query.

SELECT name, city
FROM customers
ORDER BY city ASC
LIMIT 3;

This will return the first 3 customers alphabetically by city. This is a common pattern for "Top 10" lists or "Recent Activity" feeds.

Best Practice: Use ORDER BY with LIMIT whenever you need consistent results, such as displaying the "latest 5 blog posts" or "top 10 highest-scoring players."

3. Skipping Rows Using OFFSET

Pagination (splitting results into pages) is one of the most common tasks for web developers. The OFFSET keyword allows you to skip a specific number of rows before the LIMIT starts counting.

SELECT name, city
FROM customers
ORDER BY city ASC
LIMIT 5 OFFSET 10;

This query skips the first 10 customers and then returns the next 5. In a web app, this would represent "Page 3" if you were displaying 5 items per page.

Watch Out: Large OFFSET values (e.g., OFFSET 1000000) can become very slow. The database still has to "read" through those first million rows before discarding them to give you the results you asked for.

4. Limiting Rows in Combination with Aggregates

When you use GROUP BY, the LIMIT clause is applied after the grouping and aggregation have occurred. This is perfect for finding the "Top N" categories.

SELECT city, COUNT(*) AS total_customers
FROM customers
GROUP BY city
ORDER BY total_customers DESC
LIMIT 3;

This query identifies which 3 cities have the highest concentration of customers.

 

Example Queries

1. Get the First 10 Rows of Products

Great for populating a "Preview" or "Quick Look" table in an admin dashboard.

SELECT product_name, price
FROM products
LIMIT 10;

2. Get the Top 5 Most Expensive Products

A real-world e-commerce example where you want to highlight premium items on a landing page.

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5;

3. Skip the First 5 Rows and Return the Next 10

If a user is on an inventory screen and clicks "Next" to see the second page of products.

SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 5;

4. Get the First 3 Customers from Each City

Note: While a simple LIMIT restricts the total result set, combining it with ORDER BY ensures you get a specific slice of your data.

SELECT city, name
FROM customers
ORDER BY city, name
LIMIT 3;

 

Important Notes

  • MySQL/PostgreSQL: These databases use the LIMIT and OFFSET syntax shown above.
  • SQL Server: SQL Server uses the TOP keyword (e.g., SELECT TOP 10 ...) or the OFFSET ... FETCH NEXT syntax for pagination.
  • Oracle: Modern Oracle versions use FETCH FIRST n ROWS ONLY, while older versions rely on ROWNUM.
  • Performance: Using LIMIT reduces the amount of data sent over the network from the database server to your application, which significantly speeds up response times.

 

Summary

The LIMIT clause is your primary tool for managing result set sizes and implementing pagination. By controlling how many rows are returned, you protect your system's performance and provide a better experience for your users. Just remember: if the order of those rows matters, always pair LIMIT with an ORDER BY clause to ensure your data remains predictable and accurate.

Previous