Your example is fundamentally flawed because you are not changing the order of
the data fed *into* group_concat. Although the documentation (which I am sure
someone will point you to) indicates that group_concat ordering is arbitrary,
it is not (it cannot be).
It concatenates the data fed into it, in the order that it is presented. This
may appear "arbitrary" to some, but it is not. Any sufficiently advanced
technology appears magical to the primitive observer. It is not possible for
the order to be arbitrary when the data is visited in row-wise fashion -- it is
only possible to be of limited understanding.
Consider your subselect:
> (select group_concat(chr)
> from b
> where oid = a.id
> group by oid
> order by chr
> )
The "group by" is meaningless, since the correlated subquery already only
projects the rows in a single group (determined by the where clause).
The "order by" is meaningless, since it applies to the order of the projected
results, which in this case is only one row (that is, it is applied AFTER
group_concat, not before). You statement after the meaningless cruft is
removed is equivalent to:
select a.id,
(select group_concat(chr)
from b
where oid = a.id
)
from a;
which should make the results you are seeing more understandable. The table b
is a b-tree indexed by the rowid. The table is scanned, and each of the rows
that passes the "where" clause is fed to the group_concat function. The
results are ordered by the rowid which is the default visitation order of the
rows in the table b. Baring programmatic diddling with the rowid, this is the
order in which the data was inserted into table b.
If you want to change the visitation order, you can either create an index with
the correct visitation order (index ob, below) or you can "feed the data" into
group concat in the order you wish it to be concatenated. There is no magic
here.
>sqlite
SQLite version 3.10.0 2016-01-04 23:43:47
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read gc.sql
create table a(id integer not null, primary key(id));
create table b(oid integer not null, chr char null);
insert into a values(1);
insert into a values(2);
insert into b values(1,'y');
insert into b values(1,'x');
insert into b values(2,'x');
insert into b values(2,'y');
select a.id,
(select group_concat(c)
from (select chr as c
from b
where oid = a.id
order by chr)
)
from a;
1|x,y
2|x,y
create index ob on b (oid, chr);
select a.id,
(select group_concat(chr)
from b
where oid = a.id
)
from a;
1|x,y
2|x,y
> -----Original Message-----
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Yuri
> Sent: Monday, 4 January, 2016 17:14
> To: General Discussion of SQLite Database
> Subject: [sqlite] 'order by' doesn't work with 'group_concat()'
>
> Please consider this example:
>
> ---begin---
> #!/bin/sh
>
> DB=sq.sqlite
> rm -f $DB
>
> sql() {
> echo "$1" | sqlite3 $DB
> }
>
> sql "create table a(id integer not null, primary key(id));"
> sql "create table b(oid integer not null, chr char null);"
> sql "insert into a values(1);"
> sql "insert into a values(2);"
> sql "insert into b values(1,'y');"
> sql "insert into b values(1,'x');"
> sql "insert into b values(2,'x');"
> sql "insert into b values(2,'y');"
>
> sql "select
> a.id,
> (select group_concat(chr)
> from b
> where oid = a.id
> group by oid
> order by chr
> )
> from
> a;"
> ---end---
>
> It returns this dataset:
> 1|y,x
> 2|x,y
>
> The 'order by' clause doesn't work, because if it did the result would
> have been:
> 1|x,y
> 2|x,y
>
> sqlite3-3.9.2
>
> Yuri
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
