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 author_id FETCH NEXT 1 ROWS WITH TIES
DSL.using(configuration) .selectFrom(BOOK) .orderBy(BOOK.AUTHOR_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.
Dialect support
This example using jOOQ:
select(BOOK.ID).from(BOOK).orderBy(BOOK.AUTHOR_ID).limit(1).withTies()
Translates to the following dialect specific expressions:
Aurora Postgres, CockroachDB, DB2, Firebird, Hana, MySQL, Redshift, Vertica, YugabyteDB
SELECT v0 ID
FROM (
SELECT
BOOK.ID v0,
rank() OVER (ORDER BY BOOK.AUTHOR_ID) rn
FROM BOOK
) x
WHERE rn BETWEEN (0 + 1) AND (0 + 1)
ORDER BY rn
BigQuery, DuckDB, Exasol, Snowflake
SELECT BOOK.ID FROM BOOK QUALIFY rank() OVER (ORDER BY BOOK.AUTHOR_ID) BETWEEN (0 + 1) AND (0 + 1)
ClickHouse, H2, MariaDB, Oracle, Postgres, Trino
SELECT BOOK.ID FROM BOOK ORDER BY BOOK.AUTHOR_ID FETCH NEXT 1 ROWS WITH TIES
Informix
SELECT v0 ID
FROM (
SELECT *
FROM (
SELECT
BOOK.ID v0,
rank() OVER (ORDER BY BOOK.AUTHOR_ID) rn
FROM BOOK
) x
) x
WHERE rn BETWEEN (0 + 1) AND (0 + 1)
ORDER BY rn
SQLDataWarehouse, SQLServer, Teradata
SELECT TOP 1 WITH TIES BOOK.ID FROM BOOK ORDER BY BOOK.AUTHOR_ID
Sybase
SELECT v0 ID
FROM (
SELECT TOP 1
BOOK.ID v0,
rank() OVER (ORDER BY BOOK.AUTHOR_ID) rn
FROM BOOK
) x
WHERE rn BETWEEN (0 + 1) AND (0 + 1)
ORDER BY rn
ASE, Access, Aurora MySQL, Derby, HSQLDB, MemSQL, SQLite
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!