Available in versions: Dev (3.20) | Latest (3.19) | 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.

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo