Using the SQL Row Limiting Clause

Using the SQL Row Limiting Clause

Basic Row Limiting with FETCH FIRST

The FETCH FIRST clause is used to limit the number of rows returned by a query. This clause is part of the SQL:2008 standard and is supported in Oracle 12c and later versions.

Syntax: 

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
FETCH FIRST n ROWS ONLY;

Example: 

SELECT first_name, last_name
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

In this example:

• The query selects the first_name and last_name columns from the employees table.
• It orders the results by salary in descending order.
• The FETCH FIRST 10 ROWS ONLY clause limits the result to the top 10 highest salaries.

Row Limiting with Offset

To paginate through results, you can use the OFFSET clause in combination with FETCH. The OFFSET clause specifies the number of rows to skip before starting to return rows.

Syntax: 

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
OFFSET m ROWS FETCH NEXT n ROWS ONLY;

Example: 

SELECT first_name, last_name
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

In this example:

• The query skips the first 10 rows.
• Then, it fetches the next 10 rows.
• This is useful for paginating through results. For instance, this query retrieves rows 11 through 20.

Using ROWNUM for Limiting Rows

Before Oracle 12c, row limiting was commonly done using ROWNUM. ROWNUM is a pseudocolumn that assigns a unique number to each row in the result set.

Syntax: 

SELECT *
FROM (
  SELECT column1, column2, ...
  FROM table_name
  ORDER BY column_name
)
WHERE ROWNUM <= n;

Example: 

SELECT *
FROM (
  SELECT first_name, last_name
  FROM employees
  ORDER BY salary DESC
)
WHERE ROWNUM <= 10;

In this example:

• The inner query selects rows ordered by salary in descending order.
• The outer query limits the result to the first 10 rows using ROWNUM.

Combining ROWNUM and OFFSET

You can use a combination of ROWNUM and OFFSET for more complex pagination scenarios. This approach is more involved and requires nesting queries.

Example: 

SELECT * FROM (
  SELECT a.*, ROWNUM rnum
  FROM (
    SELECT first_name, last_name
    FROM employees
    ORDER BY salary DESC
  ) a
  WHERE ROWNUM <= 20
)
WHERE rnum > 10;

 In this example:

• The inner query selects the top 20 rows ordered by salary and assigns row numbers using ROWNUM.
• The outer query filters rows to get rows 11 through 20 using the row numbers.

Examples of Row Limiting in Different Scenarios

Fetching the Top N Rows 

SELECT product_name, price
FROM products
ORDER BY price DESC
FETCH FIRST 5 ROWS ONLY;

This retrieves the 5 most expensive products.

Paginating Results 

SELECT product_name, price
FROM products
ORDER BY price
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

This skips the first 20 products and retrieves the next 10 products, which is useful for page 3 of a pagination system where each page shows 10 products.

Considerations and Best Practices

• Performance: Use ORDER BY in combination with row limiting clauses to ensure you get a consistent subset of rows.
• Indexes: Indexes on the columns used in ORDER BY can improve performance when limiting rows.
• Pagination: For large datasets, consider using pagination with OFFSET and FETCH to improve user experience and performance.

Conclusion

The SQL Row Limiting Clause provides powerful tools for controlling the number of rows returned by a query. With the FETCH FIRST, OFFSET, and ROWNUM clauses, you can efficiently manage large datasets, limit results, and paginate through data. Each method has its own use cases, and choosing the right approach depends on your specific requirements and the version of Oracle you are using.