github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_anyvalue.result (about)

     1  create table t1(a int, b int, c int);
     2  insert into t1 values(200,1,1),(100,1,2),(400,2,2),(300,2,1);
     3  SELECT distinct 1 FROM t1 group by a order by any_value(count(*)-count(b));
     4  SQL syntax error: aggregate function count calls cannot be nested
     5  SELECT distinct 1 FROM t1 group by a order by any_value(count(*))-any_value(count(b));
     6  SQL syntax error: aggregate function count calls cannot be nested
     7  SELECT DISTINCT GP1.a AS g1 FROM t1 AS GP1
     8  WHERE GP1.a >= 0
     9  ORDER BY 2+ANY_VALUE(GP1.b) LIMIT 8;
    10  SQL syntax error: for SELECT DISTINCT, ORDER BY expressions must appear in select list
    11  drop table t1;
    12  create table t1(
    13  a int,
    14  b int,
    15  c int
    16  );
    17  create table t2(
    18  a int,
    19  b int,
    20  c int
    21  );
    22  insert into t1 values(1,10,34),(2,20,14);
    23  insert into t2 values(1,-10,-45);
    24  select ANY_VALUE(t1.b) from t1 left join t2 on t1.c=t1.b and t1.a=t1.c group by t1.a;
    25  ANY_VALUE(t1.b)
    26  10
    27  20
    28  select 3+(5*ANY_VALUE(t1.b)) from t1 left join t2 on t1.c=t1.b and t1.a=t1.c group by t1.a;
    29  3+(5*ANY_VALUE(t1.b))
    30  53
    31  103
    32  delete from t1;
    33  insert into t1 (a,b) values(1,10),(1,20),(2,30),(2,40);
    34  select any_value(a), sum(b) from t1;
    35  any_value(a)	sum(b)
    36  1	100
    37  select any_value(a), sum(b) from (select * from t1 order by a desc) as d;
    38  any_value(a)	sum(b)
    39  2	100
    40  select a,any_value(b),sum(c) from t1 group by a;
    41  a	any_value(b)	sum(c)
    42  1	10	null
    43  2	30	null
    44  select a,any_value(b),sum(c) from (select * from t1 order by a desc, b desc) as d group by a;
    45  a	any_value(b)	sum(c)
    46  2	40	null
    47  1	20	null
    48  drop table t1;
    49  drop table t2;
    50  select any_value(null);
    51  any_value(null)
    52  null
    53  SELECT any_value(floor(0.5413));
    54  any_value(floor(0.5413))
    55  0
    56  SELECT any_value(floor(0.5413))-any_value(ceiling(0.553));
    57  any_value(floor(0.5413))-any_value(ceiling(0.553))
    58  -1
    59  create table t1(a tinyint, b SMALLINT, c BIGINT, d INT, e BIGINT, f FLOAT, g DOUBLE, h decimal(38,19), i DATE, k datetime, l TIMESTAMP, m char(255), n varchar(255));
    60  insert into t1 values(1, 1, 2, 43, 5, 35.5, 31.133, 14.314, "2012-03-10", "2012-03-12 10:03:12", "2022-03-12 13:03:12", "ab23c", "d5cf");
    61  insert into t1 values(71, 1, 2, 34, 5, 5.5, 341.13, 15.314, "2012-03-22", "2013-03-12 10:03:12", "2032-03-12 13:04:12", "abr23c", "3dcf");
    62  insert into t1 values(1, 1, 21, 4, 54, 53.5, 431.13, 14.394, "2011-03-12", "2015-03-12 10:03:12", "2002-03-12 13:03:12", "afbc", "dct5f");
    63  insert into t1 values(1, 71, 2, 34, 5, 5.5, 31.313, 124.314, "2012-01-12", "2019-03-12 10:03:12", "2013-03-12 13:03:12", "3abd1c", "dcvf");
    64  select any_value(a) from t1;
    65  any_value(a)
    66  1
    67  select any_value(b) from t1;
    68  any_value(b)
    69  1
    70  select any_value(c) from t1;
    71  any_value(c)
    72  2
    73  select any_value(d) from t1;
    74  any_value(d)
    75  43
    76  select any_value(e) from t1;
    77  any_value(e)
    78  5
    79  select any_value(f) from t1;
    80  any_value(f)
    81  35.5
    82  select any_value(g) from t1;
    83  any_value(g)
    84  31.133
    85  select any_value(h) from t1;
    86  any_value(h)
    87  14.3140000000000000000
    88  select any_value(i) from t1;
    89  any_value(i)
    90  2012-03-10
    91  select any_value(k) from t1;
    92  any_value(k)
    93  2012-03-12 10:03:12
    94  select any_value(l) from t1;
    95  any_value(l)
    96  2022-03-12 13:03:12
    97  select any_value(m) from t1;
    98  any_value(m)
    99  ab23c
   100  select any_value(n) from t1;
   101  any_value(n)
   102  d5cf
   103  drop table t1;
   104  select any_value(9999999999999999999999999999.9999999999);
   105  any_value(9999999999999999999999999999.9999999999)
   106  9999999999999999999999999999.9999999999
   107  select any_value("0000-00-00 00:00:00");
   108  any_value("0000-00-00 00:00:00")
   109  0000-00-00 00:00:00
   110  select any_value("你好");
   111  any_value("你好")
   112  你好
   113  drop table if exists t1;
   114  create table t1(a INT,  b float);
   115  insert into t1 values(12124, -4213.413), (12124, -42413.409);
   116  select distinct * from t1 where any_value(a)>12100;
   117  SQL syntax error: aggregate function any_value not allowed in WHERE clause
   118  drop table t1;
   119  drop table if exists t1;
   120  drop table if exists t2;
   121  create table t1(a INT,  b float);
   122  create table t2(a INT,  b float);
   123  insert into t1 values(12124, -4213.413), (1212, -42413.409);
   124  insert into t2 values(14124, -4213.413), (8479, -980.409);
   125  SELECT t1.a, t2.a FROM t1 JOIN t2 ON (any_value(t1.b) = any_value(t2.b));
   126  SQL syntax error: aggregate function any_value not allowed
   127  drop table t1;
   128  drop table t2;
   129  drop table if exists t1;
   130  create table t1(a float);
   131  insert into t1 values(14124.413), (-4213.413), (984798.123), (-980.409);
   132  select a from t1 group by a having any_value(a)<0;
   133  a
   134  -4213.41
   135  -980.409
   136  drop table t1;