Use the Order By clause in the group_concat function in SQLite

SELECT< br /> c.col1, c.col3, m.col3, m.col4,
 count(m.col1), count(re.col2) AS cnt,
 GROUP_CONCAT(p.col1 ORDER BY p.col1) AS "Group1",
 GROUP_CONCAT(p.col2 ORDER BY p.col1) AS "Group2", 
 GROUP_CONCAT(CASE WHEN con.col3 is null THEN p.col1 ELSE con.col3 END),
 con.col4, con.col5, p.col3
FROM t1 re
 INNER JOIN t2 c ON (re.col1 = c.col1)
 INNER JOIN t3 p ON (re.col2 = p.col1)
 LEFT JOIN t4 con ON (con.col1 = p.col2)
 INNER JOIN t5 m ON (m.col1 = c.col5) 
GROUP BY re.col1

table t3 
+------+------+
| col1 | col2 |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
+--- ---+------+

GROUP_CONCAT (list_order ||':' || value)

< p>I don’t think I can use the ORDER BY clause in the GROUP_CONCAT function.

Does anyone know a tricky way to implement this behavior in SQLite?

I have seen this question before. But I have a complicated query.

My statement is as follows:

SELECT< br /> c.col1, c.col3, m.col3, m.col4,
 count(m.col1), count(re.col2) AS cnt,
 GROUP_CONCAT(p.col1 ORDER BY p.col1) AS "Group1",
 GROUP_CONCAT(p.col2 ORDER BY p.col1) AS "Group2", 
 GROUP_CONCAT(CASE WHEN con.col3 is null THEN p.col1 ELSE con.col3 END),
 con.col4, con.col5, p.col3
FROM t1 re
 INNER JOIN t2 c ON (re.col1 = c.col1)
 INNER JOIN t3 p ON (re.col2 = p.col1)
 LEFT JOIN t4 con ON (con.col1 = p.col2)
 INNER JOIN t5 m ON (m.col1 = c.col5) 
GROUP BY re.col1

Group1 and Group2 come from the same table but different columns: I want to preserve the order of Group1 and Group2:

table t3 
+------+------+
| col1 | col2 |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
+--- ---+------+

So if Group1 looks like this 2,1,3 Group2 should look like this B,A,C

< p>

SQLite does not support GROUP_CONCAT ORDER BY, but you can actually fake it:

GROUP_CONCAT(list_order ||':' || value)

Then, You need to split the results in the code to return your ranking and value.