WITH TIES Clause

The WITH TIES clause in Oracle SQL is used in conjunction with the FETCH FIRST or FETCH NEXT clauses to ensure that if multiple rows share the same value in the ORDER BY clause (for example, rows with the same highest salary or latest hire date), they are included in the result set, even if they exceed the specified row limit.

This is useful for scenarios where you want to limit the number of rows returned, but you also want to make sure that if there are ties in the last row(s) (rows that share the same value in the column used in ORDER BY), those tied rows are also included.

1. Basic Syntax of WITH TIES

The syntax for the WITH TIES clause is used along with FETCH FIRST or FETCH NEXT:

SELECT column1, column2, ...

FROM table_name

ORDER BY column_name

FETCH FIRST n ROWS WITH TIES;

Where:

·        n is the number of rows you want to return, based on the ORDER BY clause.

·        WITH TIES ensures that if there are additional rows with the same value in the column(s) specified in ORDER BY as the last row returned, those rows will also be included.

2. How WITH TIES Works

When you use the FETCH FIRST n ROWS WITH TIES, the query will return the first n rows according to the ORDER BY clause, and then it will check if any additional rows have the same value in the ORDER BY column(s) as the last row in the result set. If so, those rows will also be included in the result set, even if it exceeds the specified number of rows.

Example: Without WITH TIES

SELECT employee_id, first_name, last_name, salary

FROM employees

ORDER BY salary DESC

FETCH FIRST 5 ROWS ONLY;

This query will return the top 5 employees with the highest salaries, and only 5 rows will be returned, regardless of whether multiple employees share the same salary.

Example: With WITH TIES

SELECT employee_id, first_name, last_name, salary

FROM employees

ORDER BY salary DESC

FETCH FIRST 5 ROWS WITH TIES;

This query will return the top 5 employees based on salary, but if the 5th and 6th highest salaries are the same, both employees with the same salary will be included in the result set. This means you might end up with more than 5 rows.

3. Use Cases for WITH TIES

The WITH TIES clause is particularly useful in the following scenarios:

·        Top N results with ties: When you want to fetch the top N rows but include all the rows that have the same value as the N-th row, even if it means returning more than N rows.

·        Breaking ties in a ranking: When working with ranked data (e.g., salaries, scores, or sales), and you want to include all entities that share the same rank.

·        Handling non-unique values in the last row: This is useful when dealing with non-unique values in your result set and you don't want to arbitrarily drop some rows that may have the same value as the last row in the limit set.

4. Example Scenario: Top N Salaries with WITH TIES

Consider the employees table where some employees share the same salary.

SELECT employee_id, first_name, last_name, salary

FROM employees

ORDER BY salary DESC

FETCH FIRST 3 ROWS WITH TIES;

·        If the top 3 highest salaries are 100,000, 95,000, and 95,000 (with two employees earning 95,000), this query will return all employees with the top two salaries of 100,000 and 95,000, even if that results in more than 3 rows.

·        The query will return 4 rows: the 3 employees with the highest salaries (including the two employees with a salary of 95,000).

5. Comparison: WITH TIES vs. WITHOUT TIES

·        Without WITH TIES: The query will return only the exact number of rows specified in the FETCH FIRST n ROWS ONLY clause. Ties are ignored, and the query will arbitrarily pick rows when there are multiple rows with the same value in the ORDER BY column.

·        With WITH TIES: The query will return all rows that share the same value as the last row in the FETCH FIRST n ROWS result set. This means you might end up with more rows than the number you initially requested.

6. Examples to Illustrate WITH TIES Behavior

Example 1: Basic WITH TIES Query

SELECT employee_id, first_name, last_name, salary

FROM employees

ORDER BY salary DESC

FETCH FIRST 3 ROWS WITH TIES;

Suppose the top 5 salaries are as follows:

employee_id	first_name	last_name	salary
1	John	Doe	100000
2	Jane	Smith	95000
3	Robert	Brown	95000
4	Michael	Johnson	90000
5	Sara	Lee	85000

If you fetch the top 3 salaries with ties, the result will be:

employee_id	first_name	last_name	salary
1	John	Doe	100000
2	Jane	Smith	95000
3	Robert	Brown	95000

Here, WITH TIES ensures that both Jane Smith and Robert Brown are included, even though only 3 rows were requested. If WITH TIES were omitted, only the top 3 salaries (John, Jane, and Michael) would have been returned.

Example 2: Ties for Non-Top Rows

Suppose we want to fetch the bottom 3 salaries (using ORDER BY salary ASC):

SELECT employee_id, first_name, last_name, salary

FROM employees

ORDER BY salary ASC

FETCH FIRST 3 ROWS WITH TIES;

If the lowest 3 salaries are as follows:

employee_id	first_name	last_name	salary
20	Alice	Green	50000
21	Bob	White	50000
22	Carol	Black	52000
23	Dave	Blue	54000
24	Eve	Gray	55000

The result will include Alice and Bob (both with a salary of 50,000), even though only 3 rows were requested. This means the query might return 4 rows because of the tie in the lowest salary.

employee_id	first_name	last_name	salary
20	Alice	Green	50000
21	Bob	White	50000
22	Carol	Black	52000

7. Important Considerations

·        ORDER BY is Required: The WITH TIES clause must be used with an ORDER BY clause. Without it, there would be no way to determine which rows are the "last" rows in the result set, and therefore no way to apply the tie-breaking logic.

·        Ties Based on the Last Row: Ties are evaluated based on the value of the last row that would have been returned without WITH TIES. For instance, if the 5th row is tied with the 6th row (in terms of the sorting column), the query will return both rows.

·        Performance Impact: The WITH TIES clause may introduce some overhead because the query has to check additional rows beyond the specified limit to identify ties.

8. Limitations of WITH TIES

·        Sorting Impact: Ties are only recognized based on the exact order specified in the ORDER BY clause. If two rows have the same values in the column(s) used for sorting but are different in other columns, the query will still treat them as tied and return all of them.

·        Not Available in All SQL Implementations: While WITH TIES is available in Oracle SQL 12c and later, it may not be available in other databases or in earlier versions of Oracle.