|
DATASET
create table t1 (grp int, a bigint unsigned, c char(10) , d char(10) not null);
|
insert into t1 values (1,1,NULL,"a");
|
insert into t1 values (1,10,"b","a");
|
insert into t1 values (1,11,"c","a");
|
insert into t1 values (2,2,"c","a");
|
insert into t1 values (2,3,"b","b");
|
insert into t1 values (3,4,"E","a");
|
insert into t1 values (3,5,"C","b");
|
insert into t1 values (3,6,"D","c");
|
insert into t1 values (3,7,"E","c");
|
MariaDB [test]> select grp,group_concat(c) from t1 group by grp;
|
+------+-----------------+
|
| grp | group_concat(c) |
|
+------+-----------------+
|
| 1 | b,c |
|
| 2 | c,b |
|
| 3 | E,C,D,E |
|
+------+-----------------+
|
3 rows in set (0.003 sec)
|
|
MariaDB [test]> select grp,group_concat(c limit 1,1 ) from t1 group by grp;
|
+------+----------------------------+
|
| grp | group_concat(c limit 1,1 ) |
|
+------+----------------------------+
|
| 1 | c |
|
| 2 | b |
|
| 3 | C |
|
+------+----------------------------+
|
3 rows in set (0.003 sec)
|
When I run the same query as above but wrapped in a view, then limit does not work with GROUP_CONCAT
MariaDB [test]> create view v1 as select grp,group_concat(c limit 1,1 ) from t1 group by grp;
|
Query OK, 0 rows affected (0.003 sec)
|
|
MariaDB [test]> select * from v1;
|
+------+----------------------------+
|
| grp | group_concat(c limit 1,1 ) |
|
+------+----------------------------+
|
| 1 | b,c |
|
| 2 | c,b |
|
| 3 | E,C,D,E |
|
+------+----------------------------+
|
3 rows in set (0.006 sec)
|
|
|