Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
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, Sybase, Vertica, YugabyteDB
SELECT v0 ID
FROM (
SELECT
BOOK.ID v0,
rank() OVER (ORDER BY BOOK.AUTHOR_ID) rn
FROM BOOK
) x
WHERE rn <= (0 + 1)
ORDER BY rn
BigQuery, Databricks, DuckDB, Exasol, Snowflake
SELECT BOOK.ID FROM BOOK QUALIFY rank() OVER (ORDER BY BOOK.AUTHOR_ID) <= (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 <= (0 + 1)
ORDER BY rn
Redshift
SELECT BOOK.ID FROM BOOK WHERE 1 = 1 QUALIFY rank() OVER (ORDER BY BOOK.AUTHOR_ID) <= (0 + 1)
SQLDataWarehouse, SQLServer, Teradata
SELECT TOP 1 WITH TIES BOOK.ID FROM BOOK ORDER BY BOOK.AUTHOR_ID
ASE, Access, Aurora MySQL, Derby, HSQLDB, MemSQL, SQLite
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!