Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
WITH TIES clause
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The previous chapter talked about the LIMIT clause, which limits the result set to a certain number of rows. The SQL standard specifies the following syntax:
OFFSET m { ROW | ROWS }
FETCH { FIRST | NEXT } n { ROW | ROWS } { ONLY | WITH TIES }
By default, most users will use the semantics of the ONLY keyword, meaning a LIMIT 5 expression (or FETCH NEXT 5 ROWS ONLY expression) will result in at most 5 rows. The alternative clause WITH TIES will return at most 5 rows, except if the 5th row and the 6th row (and so on) are "tied" according to the ORDER BY clause, meaning that the ORDER BY clause does not deterministically produce a 5th or 6th row. For example, let's look at our book table:
SELECT * FROM book ORDER BY actor_id FETCH NEXT 1 ROWS WITH TIES
DSL.using(configuration) .selectFrom(BOOK) .orderBy(BOOK.ACTOR_ID) .limit(1).withTies() .fetch();
Resulting in:
id actor_id title --------------------- 1 1 1984 2 1 Animal Farm
We're now getting two rows because both rows "tied" when ordering them by ACTOR_ID. The database cannot really pick the next 1 row, so they're both returned. If we omit the WITH TIES clause, then only a random one of the rows would be returned.
Not all databases support WITH TIES. Oracle 12c supports the clause as specified in the SQL standard, and SQL Server knows TOP n WITH TIES without OFFSET support.
Feedback
Do you have any feedback about this page? We'd love to hear it!