Using ORDER BY clause inside GROUP_CONCAT function in SQLite

sqlite aggregate functions
sqlite group_concat(distinct separator)
sqlite concat
sqlite group_concat multiple columns
sqlite reverse group_concat
sqlite group_concat sorted
sqlite difference function
group_concat separator

I don't think that I can use ORDER BY clause inside the GROUP_CONCAT function.

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

I saw this question before. But I have a complex query .

My statement looks like this:

SELECT
    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 is coming from the same table but different columns: I want to preserve the order of the Group1 with Group2:

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

so if Group1 appears like this 2,1,3 Group2 should appear like this B,A,C

SQLite doesn't support ORDER BY inside a GROUP_CONCAT, but you can actually fake it:

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

Then you need to split the result in code in order to get back your sort and value.

Sqlite group_concat ordering, Can you not use a subselect with the order by clause in, and then that all aggregate functions respect the ordering inside a window frame, but� I don't think that I can use ORDER BY clause inside the GROUP_CONCAT function. For me it fails at ORDER (SQLite 3.4.7) – zapl Apr 2 '12 at 16:15.

To avoid any indeterminism you can use recursive CTE like this:

sqlite> create table t3(pos,col1,col2);
sqlite> insert into t3 values(1,2,'B'),(2,1,'A'),(3,5,'E');
sqlite> select * from t3;
1|2|B
2|1|A
3|5|E
sqlite>
with
  sorted(pos,c1,c2) as (
    select row_number() over (order by t3.pos), -- sorting by first column's value
      t3.col1, t3.col2
      from t3
  ),
  concat(pos,c1,c2) as (
    select sorted.pos,sorted.c1,sorted.c2  -- starting with values for first position
      from sorted
     where sorted.pos=1
     union all
    select sorted.pos,
           concat.c1||','||sorted.c1,  -- adding next value from col1
           concat.c2||','||sorted.c2   -- adding next value from col2
      from concat
      join sorted
        on concat.pos+1 = sorted.pos   -- going through subsequent positions
  )
select c1, c2
  from concat
 order by pos desc
 limit 1;  -- order by desc limit 1 means 'take the row with largest number'

2,1,5|B,A,E

Although quite elaborate, this solution guarantees proper sorting and can easily be extended with more columns. The ordering column can have gaps - the sorted CTE takes care of making it into a proper integer sequence.

Note that row_number() over (order by...) may require a decently recent version of sqlite which supports windowing functions.

SQLite GROUP_CONCAT Funtion: Concat Non-NULL Values in a , The GROUP_CONCAT() function is an aggregate function that concatenates all non-null values in a column. If you skip the separator, the GROUP_CONCAT() function uses a comma (“,”) by default. AlbumId GROUP BY Title ORDER BY Title; First, the GROUP BY clause divides the tracks by album into groups. Then � Example: SQLite group_concat() with order by. The following SQLite statement will return unique “cate_id”s , as a list of strings separated by the commas, in ascending order for each group of 'pub_id' from the book_mast table. The order can be changed in descending, using 'DESC' instead of 'ASC' at the end of the select statement.

What about something like this?

SELECT 
    col1, col3, col3, col4,
    count(col1), count(re.col2) AS cnt,
    GROUP_CONCAT(p.col1) AS "Group1",
    GROUP_CONCAT(p.col2) AS "Group2", 
    GROUP_CONCAT(CASE WHEN con.col3 is null THEN p.col1 ELSE con.col3 END),
    con.col4, con.col5, p.col3
FROM (
    SELECT 
        *
    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) 
    ORDER BY
        p.col1 ASC,
        p.col2 ASC
)
GROUP BY re.col1 

I Haven't tested it but if you can share some data...

SQLite Aggregate Functions: An Essential Guide to Aggregate , This tutorial shows you how to use the SQLite aggregate functions to find the maximum, syntax of calling an aggregate function except for the GROUP_CONCAT() function: FROM tracks GROUP BY AlbumId ORDER BY track_count DESC;. First, the GROUP BY clause divides the tracks by album into groups. Then, the GROUP_CONCAT() function applies to each group to concatenate track names. In this tutorial, you have learned how to use the SQLite GROUP_CONCAT() function to concatenates all non-null values in a column.

I have tried this and it make the work

SELECT
    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 (
        SELECT col1, col2, col3, col4, col5 FROM t3 ORDER BY col1
    ) AS          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 

Built-in Aggregate Functions, Additional aggregate functions written in C may be added using the If a FILTER clause is provided, then only rows for which the expr is true are included in the The avg() function returns the average value of all non-NULL X within a group. The group_concat() function returns a string which is the concatenation of all� Because the GROUP_CONCAT function is an aggregate function, to sort the values, you must use the ORDER BY clause inside the function, not in the ORDER BY in the SELECT statement. The following example demonstrates the incorrect usage of the ORDER BY clause in the context of using the GROUP_CONCAT function:

MySQL GROUP_CONCAT Function By Practical Examples, This tutorial shows you how to use the MySQL GROUP_CONCAT function to The ORDER BY clause allows you to sort the values in ascending or It means you cannot use the result of the GROUP_CONCAT() function for IN operator e.g., within a All MySQL tutorials are practical and easy-to-follow, with SQL script and� SQLite GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. Following is the basic syntax of GROUP BY clause. GROUP BY clause must follow the

SQLite group_concat() function, Example: SQLite group_concat() with order by The order can be changed in descending, using 'DESC' instead of 'ASC' at the end of the select statement. For what's worth, here is the syntax adopted by Oracle for its LISTAGG function [1]: LISTAGG( expression, delimiter ) WITHIN GROUP ( ORDER BY clause ) As group_concat already supports 'distinct', perhaps tagging 'order by' at the end of the expression would not be that bad: GROUP_CONCAT( DISTINCT expression ORDER BY clause, delimiter ) EIther

'order by' doesn't work with 'group_concat()', bin/sh DB=sq.sqlite rm -f $DB sql() { echo "$1" | sqlite3 $DB } sql "create table a( id The group_concat() function returns a string which is the "Distinct" and "Order by" in "group_concat". Hi, I have two questions/observations/suggestions related to SQLite function "group_concat". This function is extremely

Comments
  • You write in your answer that the query does what you need. What is the question then?
  • @newtover the syntax he used is supported by mysql not sqlite.
  • Possible duplicate of Sqlite group_concat ordering
  • Can you elaborate a bit? I don't understand your answer.
  • @nowox You cannot sort directly inside a GROUP_CONCAT with SQLite so this command will concatenate the list_order with a separator (here I use ":") and the value. Then when parsing the results (for me I was using Android) you save the data in a list and reorder it based on the list_order.
  • Is the GROUP_CONCAT(column ORDER BY some_column) syntax really supported by SQLite? For me it fails at ORDER (SQLite 3.4.7)
  • This does not work in sqlite. It fails at ORDER like zapl said above
  • @confucius I think the key here is that you rewrote t3 as ordered, but forgot to remove the ORDER BY p.col1 from the GROUP_CONCATs.