Declaration
ARBITRARY(expr)
Description
This function is used to non-deterministically return any non-NULL value from a set of rows.
Examples
Create the test table
test_tbl1.obclient> CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50), col3 INT);Insert data into the
test_tbl1table.obclient> INSERT INTO test_tbl1 VALUES (1, 'A001', 100), (2, 'A001', NULL), (3, 'A001', 110), (4, 'B002', 100), (5, 'B002', 130), (6, 'C001', 160);Group by the
col2column and randomly return a non-null value from thecol3column within each group.obclient> SELECT col2, ARBITRARY(col3) FROM test_tbl1 GROUP BY col2;The return result is as follows:
+------+-----------------+ | col2 | ARBITRARY(col3) | +------+-----------------+ | A001 | 100 | | B002 | 100 | | C001 | 160 | +------+-----------------+ 3 rows in set