Sqlite group_concat ordering

sqlite group-concat
user230781 picture user230781 · Dec 13, 2009 · Viewed 19.7k times · Source

In Sqlite I can use group_concat to do:

1...A
1...B
1...C
2...A
2...B
2...C

1...C,B,A
2...C,B,A

but the order of the concatenation is random - according to docs.

I need to sort the output of group_concat to be

1...A,B,C
2...A,B,C

How can I do this?

Answer

Adriaan Stander picture Adriaan Stander · Dec 13, 2009

Can you not use a subselect with the order by clause in, and then group concat the values?

Something like

SELECT ID, GROUP_CONCAT(Val)
FROM (
   SELECT ID, Val
   FROM YourTable
   ORDER BY ID, Val
   )
GROUP BY ID;

Related questions

GROUP_CONCAT in SQLite

I am having data like this 1 A 1 B 1 C 1 D 2 E 2 F 3 G 3 H 3 I 3 J 3 K by using this query select ABSTRACTS_ITEM._id,Name from ABSTRACTS_ITEM , ABSTRACT_AUTHOR , AUTHORS_ABSTRACT where ABSTRACTS_ITEM._id = AUTHORS_ABSTRACT.ABSTRACTSITEM_…

Read More
sqlite3 JOIN, GROUP_CONCAT using distinct with custom separator

Given a table of "events" where each event may be associated with zero or more "speakers" and zero or more "terms", those records associated with the events through join tables, I need to produce a table of all events with …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More