ARBITRARY

Syntax

ARBITRARY(expr)

Purpose

This function returns a non-NULL value randomly from a set of rows.

Examples

1. Create a test table named test_tbl1.

   obclient> CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50), col3 INT);
   

2. Insert data into the test_tbl1 table.

   obclient> INSERT INTO test_tbl1 VALUES
       (1, 'A001', 100),
       (2, 'A001', NULL),
       (3, 'A001', 110),
       (4, 'B002', 100),
       (5, 'B002', 130),
       (6, 'C001', 160);
   

3. Group the data by the col2 column and return a non-null value from the col3 column randomly for 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