Limiting Results with LIMIT and OFFSET in SQL

Limiting Results with LIMIT and OFFSET in SQL

When working with large datasets, you may want to retrieve only a subset of rows rather than the entire result set. SQL provides the LIMIT and OFFSET clauses to help you control the number of rows returned by your query and to specify which rows to start from. This is particularly useful for pagination, where data is displayed in smaller chunks across multiple pages.

1. Using LIMIT to Fetch a Subset of Rows

The LIMIT clause is used to specify the maximum number of rows to return in the result set. This helps in reducing the amount of data retrieved, making queries faster and more efficient, especially when dealing with large tables.

Syntax:

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;

  • number_of_rows: The maximum number of rows to return.

Example:

SELECT first_name, last_name, salary 
FROM employees
ORDER BY salary DESC
LIMIT 3;

In this example:

  • The SELECT statement retrieves the first_name, last_name, and salary columns from the employees table.

  • The ORDER BY clause sorts the results by salary in descending order.

  • The LIMIT 3 clause restricts the result set to the top 3 rows.

Result:

first_name
last_name
salary

Ravi

Verma

60000.00

Amit

Sharma

55000.00

Neha

Singh

45000.00

In this output:

  • Only the top 3 employees with the highest salaries are returned.

2. Using OFFSET to Skip Rows

The OFFSET clause is used to skip a specified number of rows before starting to return rows from the result set. This is often used in combination with LIMIT for pagination.

Syntax:

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows
OFFSET number_of_rows_to_skip;

  • number_of_rows_to_skip: The number of rows to skip before starting to return rows.

Example:

SELECT first_name, last_name, salary 
FROM employees
ORDER BY salary DESC
LIMIT 3
OFFSET 2;

In this example:

  • The SELECT statement retrieves the first_name, last_name, and salary columns from the employees table.

  • The ORDER BY clause sorts the results by salary in descending order.

  • The LIMIT 3 clause restricts the result set to 3 rows.

  • The OFFSET 2 clause skips the first 2 rows and then returns the next 3 rows.

Result:

first_name
last_name
salary

Neha

Singh

45000.00

Suresh

Mehta

40000.00

Anjali

Patel

35000.00

In this output:

  • The query skips the top 2 highest-paid employees and then returns the next 3 employees in the list.

3. Combining LIMIT and OFFSET for Pagination

Pagination is a common use case for LIMIT and OFFSET, where data is divided into pages, and each page displays a fixed number of rows. By adjusting the OFFSET value, you can retrieve different pages of data.

Example:

Imagine you want to display 5 employees per page. Here’s how you can fetch different pages:

  • Page 1: Retrieve the first 5 rows.

SELECT first_name, last_name, salary 
FROM employees
ORDER BY salary DESC
LIMIT 5
OFFSET 0;

  • Page 2: Retrieve the next 5 rows.

SELECT first_name, last_name, salary 
FROM employees
ORDER BY salary DESC
LIMIT 5
OFFSET 5;

  • Page 3: Retrieve the next 5 rows after that.

SELECT first_name, last_name, salary 
FROM employees
ORDER BY salary DESC
LIMIT 5
OFFSET 10;

In each query:

  • The LIMIT 5 ensures that only 5 rows are returned.

  • The OFFSET value is adjusted to skip the appropriate number of rows, allowing you to fetch different pages of data.

4. Example with LIMIT and OFFSET

Let’s say you have a list of employees, and you want to display 3 employees per page. You can use LIMIT and OFFSET to fetch different pages of the result set.

Fetching Page 1 (First 3 Employees):

SELECT first_name, last_name, salary 
FROM employees
ORDER BY salary DESC
LIMIT 3
OFFSET 0;

Fetching Page 2 (Next 3 Employees):

SELECT first_name, last_name, salary 
FROM employees
ORDER BY salary DESC
LIMIT 3
OFFSET 3;

Fetching Page 3 (Next 3 Employees):

SELECT first_name, last_name, salary 
FROM employees
ORDER BY salary DESC
LIMIT 3
OFFSET 6;

In these examples:

  • Page 1: The first query retrieves the top 3 employees.

  • Page 2: The second query skips the first 3 employees and retrieves the next 3.

  • Page 3: The third query skips the first 6 employees and retrieves the next 3.

5. Practical Use Case: Displaying Search Results

When implementing search functionality on a website, it’s common to display results across multiple pages. By using LIMIT and OFFSET, you can control how many results are shown per page and navigate between pages efficiently.

Example:

SELECT product_name, price 
FROM products
WHERE category = 'Electronics'
ORDER BY price ASC
LIMIT 10
OFFSET 20;

In this example:

  • The WHERE clause filters the products to only those in the Electronics category.

  • The ORDER BY clause sorts the products by price in ascending order.

  • The LIMIT 10 clause restricts the result set to 10 rows.

  • The OFFSET 20 clause skips the first 20 rows, effectively displaying the 3rd page of results (assuming 10 results per page).

Conclusion

The LIMIT and OFFSET clauses in SQL are powerful tools for managing large datasets and implementing pagination. By using LIMIT to control the number of rows returned and OFFSET to skip a specific number of rows, you can efficiently retrieve and display subsets of data. Understanding how to use these clauses is essential for optimizing queries and improving the user experience in applications that display data in pages.

This tutorial is brought to you by codes with pankaj.

PreviousSorting Data with ORDER BY in SQLNextRemoving Duplicates with DISTINCT in SQL

Last updated