This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
DELETE .. ORDER BY .. LIMIT
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The ORDER BY and LIMIT clauses allow for deleting only a subset of the data in a table, based on their ordering. This can be useful to implement queue semantics, e.g. to delete only the top row, and possibly return it in one go.
Dialect support
This example using jOOQ:
deleteFrom(BOOK).orderBy(BOOK.ID.asc()).limit(1)
Translates to the following dialect specific expressions:
-- AURORA_MYSQL, COCKROACHDB, DB2, MARIADB, MYSQL, TRINO
DELETE FROM BOOK
ORDER BY BOOK.ID ASC
LIMIT 1
-- AURORA_POSTGRES, DUCKDB, EXASOL, HANA, HSQLDB, REDSHIFT, SNOWFLAKE, SQLITE, VERTICA, YUGABYTEDB
DELETE FROM BOOK
WHERE BOOK.ID IN (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID ASC
LIMIT 1
)
-- BIGQUERY
DELETE FROM BOOK
WHERE BOOK.ID IN (
SELECT BOOK.ID
FROM BOOK
WHERE TRUE
ORDER BY BOOK.ID ASC
LIMIT 1
)
-- DERBY, H2, ORACLE, POSTGRES
DELETE FROM BOOK
WHERE BOOK.ID IN (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID ASC
FETCH NEXT 1 ROWS ONLY
)
-- FIREBIRD
DELETE FROM BOOK
ORDER BY BOOK.ID ASC
ROWS 1
-- INFORMIX
DELETE FROM BOOK
WHERE BOOK.ID IN (
SELECT *
FROM (
SELECT FIRST 1 BOOK.ID
FROM BOOK
ORDER BY BOOK.ID ASC
) x
)
-- MEMSQL
DELETE FROM BOOK
WHERE BOOK.ID IN (
SELECT *
FROM (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID ASC
LIMIT 1
) t
)
-- SQLDATAWAREHOUSE, SQLSERVER, SYBASE
DELETE FROM BOOK
WHERE BOOK.ID IN (
SELECT TOP 1 BOOK.ID
FROM BOOK
ORDER BY BOOK.ID ASC
)
-- TERADATA
DELETE FROM BOOK
WHERE BOOK.ID IN (
SELECT *
FROM (
SELECT TOP 1 BOOK.ID
FROM BOOK
ORDER BY BOOK.ID ASC
) x
)
-- ACCESS, ASE
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!