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, HSQLDB, Hana, Redshift, SQLite, Snowflake, 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 )
ClickHouse
DELETE FROM BOOK WHERE 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
)
ASE, Access
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!