Assume I create the following table:
CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT);
INSERT INTO Test (Value) VALUES('Alpha');
INSERT INTO Test (Value) VALUES('Beta');
INSERT INTO Test (Value) VALUES('Beta');
INSERT INTO Test (Value) VALUES('Alpha');
According to the documentation of group_concat the order is undefined, indeed:
SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value ASC;
SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value DESC;
Both queries result in Alpha,Beta.
Changing the queries to
WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value ASC) SELECT
group_concat(x) FROM Result;
WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value DESC) SELECT
group_concat(x) FROM Result;
leads to the results Alpha,Beta, respectively Beta,Alpha.
Is this a coincidence or is this guaranteed to work?
Are there any other solutions / possibilities?
Regards,
Hardy
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
