Available in versions: Dev (3.21)
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.
ARRAY_REVERSE
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The ARRAY_REVERSE function allows for replacing all reversing the order of elements in an array:
SELECT array_reverse(ARRAY[1, 2, 3])
create.select(arrayReverse(array(1, 2, 3))).fetch();
The result would look like this:
+---------------+ | array_reverse | +---------------+ | [3, 2, 1] | +---------------+
Dialect support
This example using jOOQ:
arrayReverse(array(1, 2, 3))
Translates to the following dialect specific expressions:
Aurora Postgres, H2, HSQLDB
CASE
WHEN ARRAY[1, 2, 3] IS NULL THEN ARRAY[1, 2, 3]
WHEN cardinality(ARRAY[1, 2, 3]) = 0 THEN ARRAY[1, 2, 3]
ELSE (
SELECT array_agg(e ORDER BY o DESC)
FROM UNNEST(ARRAY[1, 2, 3]) WITH ORDINALITY t (e, o)
)
END
BigQuery, Postgres
array_reverse(ARRAY[1, 2, 3])
Databricks
CASE
WHEN ARRAY(1, 2, 3) IS NULL THEN ARRAY(1, 2, 3)
WHEN cardinality(ARRAY(1, 2, 3)) = 0 THEN ARRAY(1, 2, 3)
ELSE (
SELECT array_agg(e ORDER BY o DESC)
FROM (
SELECT
array_table.COLUMN_VALUE,
row_number() OVER (ORDER BY (
SELECT 1
)) ordinal
FROM EXPLODE(ARRAY(1, 2, 3)) array_table (COLUMN_VALUE)
) t (e, o)
)
END
DuckDB
CASE
WHEN ARRAY[1, 2, 3] IS NULL THEN ARRAY[1, 2, 3]
WHEN array_length(ARRAY[1, 2, 3]) = 0 THEN ARRAY[1, 2, 3]
ELSE (
SELECT array_agg(e ORDER BY o DESC)
FROM (
SELECT
array_table.COLUMN_VALUE,
row_number() OVER () ordinal
FROM UNNEST(ARRAY[1, 2, 3]) array_table (COLUMN_VALUE)
) t (e, o)
)
END
Spanner
CASE
WHEN ARRAY[1, 2, 3] IS NULL THEN ARRAY[1, 2, 3]
WHEN array_length(ARRAY[1, 2, 3]) = 0 THEN ARRAY[1, 2, 3]
ELSE (
SELECT array_agg(e ORDER BY o DESC)
FROM (
SELECT
null e,
null o
FROM UNNEST([STRUCT(1 AS dual)]) AS dual
WHERE FALSE
UNION ALL
SELECT *
FROM (
SELECT
COLUMN_VALUE,
(offset + 1) ordinal
FROM UNNEST(ARRAY[1, 2, 3]) AS COLUMN_VALUE WITH OFFSET
) t
) t
)
END
Trino
reverse(ARRAY[1, 2, 3])
ASE, Access, Aurora MySQL, ClickHouse, CockroachDB, DB2, Exasol, Firebird, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Vertica, YugabyteDB
/* 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!