Results 1 to 5 of 5

Thread: [SQLite] How to get an Order within a GROUP_CONCAT

  1. #1

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,514

    [SQLite] How to get an Order within a GROUP_CONCAT

    In another Forum i helped out an User who was struggling with the following:
    He had a table "tbl_employee" and a table "tbl_task", which were connected in an "m:m"-scenario via "tbl_emp_task"

    He wanted to query his SQLite-Database to show for all employees their assigned tasks, but the tasks as a single text, preferably in alphabetic order
    Example
    employee task
    John Smith task1, task4, task6
    Robert Miller task3, task4, task5

    The GROUP_CONCAT-Function of SQLite doesn't have a ORDER BY-Modifier.
    The documenation on sqlite.org even says:
    The order of the concatenated elements is arbitrary.
    Though i think, the order is the order the elements are "presented", in case of a base-table my guess is the (hidden) row_id

    Therefore i developed a solution as follows:
    Setup for test-Database
    Code:
    CREATE TABLE "tbl_employee" (
        "ID"    INTEGER,
        "FirstName"    TEXT NOT NULL,
        "LastName"    TEXT NOT NULL,    
        PRIMARY KEY("ID")
    );
    CREATE TABLE "tbl_task" (
        "ID"    INTEGER,
        "Description"    TEXT NOT NULL,
        PRIMARY KEY("ID")
    );
    CREATE TABLE "tbl_emp_task" (
        "emp_id"    INTEGER,
        "task_id"    INTEGER,
        FOREIGN KEY("task_id") REFERENCES "tbl_task"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY("emp_id") REFERENCES "tbl_employee"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
        PRIMARY KEY("emp_id","task_id")
    );
    INSERT INTO tbl_employee ("ID", "FirstName", "LastName") VALUES (1, 'Tom', 'Brown'), (2, 'Robert', 'Miller'), (3, 'John', 'Smith');
    INSERT INTO tbl_task ("ID", "Description") VALUES (1, 'answering phones'), (2, 'preparing meeting'), (3, 'receiving visitors'), (4, 'Maintaining security'), (5, 'Arranging couriers');
    INSERT INTO tbl_emp_task ("emp_id", "task_id") VALUES (1,1), (1,2), (1,4), (2,2),(2,3),(2,5);
    Note the combined Primary Key for tbl_emp_task consisting of the Foreign Keys.
    That way we can make sure that each employee can be assigned a specific task only once


    and here the solution:
    Code:
    With CTE(EID,TDESC) AS
        (SELECT
            ET.emp_ID,
            T.Description
        FROM tbl_emp_task AS ET
        INNER JOIN tbl_task AS T
        ON T.ID=ET.task_id
        ORDER BY Lower(T.Description))
    SELECT
        E.FirstName || ' ' || E.LastName As FullName,
        COALESCE(C.AssignedTasks, 'No assigned Tasks') AS AssignedTasks
    FROM tbl_employee AS E
    LEFT JOIN
        (SELECT
            EID,
            GROUP_CONCAT(TDESC, ', ') AS AssignedTasks
        FROM CTE GROUP BY EID) AS C
    ON C.EID=E.ID
    GROUP BY E.FirstName || ' ' || E.LastName ORDER BY E.LastName, E.FirstName
    If you want Case-Sensitive order, remove the "Lower"-Function within the CTE

    Try it out: https://dbfiddle.uk/?rdbms=sqlite_3....f7da10287c6885
    Last edited by Zvoni; Jul 14th, 2022 at 07:48 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  2. #2
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,225

    Re: [SQLite] How to get an Order within a GROUP_CONCAT

    Why do you need FROM (SELECT EID,TID,TDESC FROM CTE) GROUP BY EID instead of just FROM CTE GROUP BY EID?

    cheers,
    </wqw>

  3. #3

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,514

    Re: [SQLite] How to get an Order within a GROUP_CONCAT

    Quote Originally Posted by wqweto View Post
    Why do you need FROM (SELECT EID,TID,TDESC FROM CTE) GROUP BY EID instead of just FROM CTE GROUP BY EID?

    cheers,
    </wqw>
    you're right
    It has to do with that i've started to explicitly select columns for JOINS, so call it personal preference
    (And yes, TID as Output-Column for the CTE is not necessary. It's a leftover, when i was playing with it)

    EDIT: I've edited my original post to reflect wqweto's comment as well as removed TID from the CTE, and replaced the link to the new fiddle
    Last edited by Zvoni; Jul 14th, 2022 at 07:47 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,225

    Re: [SQLite] How to get an Order within a GROUP_CONCAT

    Btw, sqlite lacks ORDER BY on GROUP_CONCAT but uses the *physical* order of the source data i.e. using FROM (SELECT * FROM CTE ORDER BY TDESC DESC) GROUP BY EID instead of FROM CTE GROUP BY EID reverses the order of concatenated texts so you probably would want to reverse the change above but add an explicit ORDER BY (sorry about that).

    cheers,
    </wqw>

  5. #5

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,514

    Re: [SQLite] How to get an Order within a GROUP_CONCAT

    Quote Originally Posted by wqweto View Post
    Btw, sqlite lacks ORDER BY on GROUP_CONCAT but uses the *physical* order of the source data i.e. using FROM (SELECT * FROM CTE ORDER BY TDESC DESC) GROUP BY EID instead of FROM CTE GROUP BY EID reverses the order of concatenated texts so you probably would want to reverse the change above but add an explicit ORDER BY (sorry about that).

    cheers,
    </wqw>
    I already quoted the SQLite-doc's, that there is an "arbitrary" Order for GROUP_CONCAT, though i also wrote my thoughts on it.

    Would have to disagree with your proposal.
    A CTE is basically a temp. VIEW, and the CTE itself is already ordering the Task-Descriptions case-insensitive (Lower-Function in the ORDER BY inside the CTE).
    So the CTE is "presenting" the Data in a different order than the "physical" Data in the Base-Table, but since the GROUP_CONCAT's source is the CTE, and not the Base-table, the Order is correct.

    Though, i wouldn't mind a Feedback of tests with a significiant amount of Data

    And you have no reason to be sorry.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width