"order by" is applied to the OUTPUT of the select and does absolutely nothing
with respect of the traversal order of the underlying table when you are
selecting an aggregate.
So, the reason that you are seeing a different ordering is more likely
connected to the use of the "where enable == 1" than anything else.
Why do you not ask SQLite to "explain" to you what it is doing?
If you want the results of a group_concat to be in a specific order, then you
must control the in which the underlying data is fed to the aggregate --
sorting the aggregate result after the fact is sort of like washing the
pesticides off an apple after it has already been eaten -- completely
non-productive.
Something like:
select group_concat(name, ' ')
from (select name
from moca_config
where <whatever conditions you want>
order by name) as T1;
may be more what you are seeking ... if you want the output of group_concat to
be ordered, you need to control what goes INTO the function, not what is coming
out.
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Bob Friesenhahn
>Sent: Tuesday, 15 August, 2017 09:13
>To: SQLite mailing list
>Subject: [sqlite] group_concat() reverses order given where clause?
>
>I am surprised by this behavior of group_concat():
>
>sqlite> select group_concat(name, ' ') AS 'names' from moca_config
>order by name;
>names
>bonding cof lof_update moca_core_trace_enable preferred_nc rf_band
>verbose
>sqlite> select group_concat(name, ' ') AS 'names' from moca_config
>where enable == 1 order by name;
>names
>rf_band verbose moca_core_trace_enable preferred_nc lof_update
>bonding
>
>Notice that adding a 'where' clause has caused the order to be
>reversed from what was requested in the query. Why is this and what
>can I do to correct it?
>
>Thanks,
>
>Bob
>--
>Bob Friesenhahn
>bfrie...@simple.dallas.tx.us,
>http://www.simplesystems.org/users/bfriesen/
>GraphicsMagick Maintainer, http://www.GraphicsMagick.org/
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
