All versions: 3.11 | 3.10 | Development versions: 3.12

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.

The jOOQ Logo