Using the SQL Row Limiting Clause: WITH TIES
Overview of WITH TIES
The WITH TIES clause is used in conjunction with the FETCH clause to include additional rows that have the same values as the last row retrieved within the specified limit. This is particularly useful when you want to ensure that rows with values equal to the value of the boundary row are included in the results, even if this exceeds the initial row limit.
Syntax:
SELECT column1, column2, ... FROM table_name ORDER BY column_name FETCH FIRST n ROWS WITH TIES;
- FETCH FIRST n ROWS WITH TIES: Retrieves the first n rows and includes all rows that have the same value as the n-th row.
Basic Usage of WITH TIES
Example 1: Limiting Rows with WITH TIES
Suppose you have a products table, and you want to get the top 5 most expensive products, but also include any products that have the same price as the 5th most expensive product.
SELECT product_name, price FROM products ORDER BY price DESC FETCH FIRST 5 ROWS WITH TIES;
In this example:
- The query selects product_name and price columns from the products table.
- The results are sorted by price in descending order.
- FETCH FIRST 5 ROWS WITH TIES retrieves the top 5 most expensive products and includes all products that have the same price as the 5th most expensive product.
Practical Examples
Example 2: Retrieving Employees with the Same Salary
Suppose you want to retrieve the top 3 highest-paid employees, but you also want to include any employees who have the same salary as the 3rd highest-paid employee.
SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC FETCH FIRST 3 ROWS WITH TIES;
In this example:
- The query selects first_name, last_name, and salary columns from the employees table.
- The results are sorted by salary in descending order.
- FETCH FIRST 3 ROWS WITH TIES retrieves the top 3 highest-paid employees and includes any employees with the same salary as the 3rd highest-paid employee.
Considerations and Best Practices
- Usage: Use WITH TIES when you want to ensure that all rows with values equal to the boundary value are included, even if this increases the number of rows beyond the specified limit.
- Performance: Be aware that using WITH TIES can lead to a larger result set, which might affect performance, especially with large datasets.
- Sorting: Ensure that the ORDER BY clause is appropriately set to determine the boundary values used with WITH TIES. The clause is based on the last row of the ordered result set to determine which rows to include.
Compatibility and SQL Standards
- The WITH TIES clause is supported in Oracle 12c and later versions, as well as other SQL databases that conform to SQL:2008 and later standards.
- For earlier versions of Oracle or other SQL databases that do not support WITH TIES, you may need to use alternative approaches or complex queries to achieve similar results.
Conclusion
The WITH TIES clause provides a flexible way to limit the number of rows returned by a query while including all rows that have the same value as the last row in the limited set. It is especially useful when you need to include rows with shared values at the boundary of the result set.