Error when including group by and order by in union all operator-CodePudding

SELECT productspec_crud.id as id,clause_no FROM design_models 
INNER JOIN productspec_crud ON productspec_crud.prod_id=design_models.prod_id 
WHERE design_models.id = '13' 
union all 
select design_input_general.id,clause_no from design_input_general 
left join design_io on design_io.model_id='13' and design_io.design_input_general_id=design_input_general.id

From the above working code, I was trying to include few code to sort the rows based on clause_no values in both select queries. Below is the code which I want to include:

JOIN (SELECT 1 num UNION SELECT 2 UNION SELECT 3
      UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) numbers 
ON LENGTH(clause_no) - LENGTH(REPLACE(clause_no, '.', '')) >= num - 1
GROUP BY id, clause_no
ORDER BY GROUP_CONCAT(CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(clause_no, '.', num), '.', -1) REGEXP '[^0-9]'
                           THEN RPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(clause_no, '.', num), '.', -1), 8, 0) 
                           ELSE LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(clause_no, '.', num), '.', -1), 8, 0) 
                           END ORDER BY num SEPARATOR '');

I've included the above code in this way:

(SELECT 
        productspec_crud.id as id,clause_no FROM `design_models` 
        INNER JOIN `productspec_crud` ON `productspec_crud`.`prod_id`=`design_models`.`prod_id`
        JOIN (SELECT 1 num UNION SELECT 2 UNION SELECT 3
              UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) numbers 
            ON LENGTH(clause_no) - LENGTH(REPLACE(clause_no, '.', '')) >= num - 1)
union all
(select design_input_general.id,clause_no from `design_input_general` 
        left join design_io on design_io.model_id='13' and design_io.design_input_general_id=design_input_general.id
        JOIN (SELECT 1 num UNION SELECT 2 UNION SELECT 3
              UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) numbers 
            ON LENGTH(clause_no) - LENGTH(REPLACE(clause_no, '.', '')) >= num - 1)) 
GROUP BY id, clause_no
ORDER BY GROUP_CONCAT(CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(clause_no, '.', num), '.', -1) REGEXP '[^0-9]'
                           THEN RPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(clause_no, '.', num), '.', -1), 8, 0) 
                           ELSE LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(clause_no, '.', num), '.', -1), 8, 0) 
                           END ORDER BY num SEPARATOR '');

When I include this I'm getting the below error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'GROUP BY `id`, `clause_no` ORDER BY GROUP_CONCAT(CASE WHEN SUBSTRING_INDEX(SU...'

Can anyone suggest the right syntax to include this in an union all operator.

CodePudding user response：

You will have to use GROUP BY against a subquery of the union:

SELECT id, clause_no
FROM
(
    SELECT psc.id AS id, clause_no, num
    FROM design_models dm
    INNER JOIN productspec_crud psc ON psc.prod_id = dm.prod_id
    INNER JOIN (SELECT 1 num UNION SELECT 2 UNION SELECT 3
          UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) numbers 
        ON LENGTH(clause_no) - LENGTH(REPLACE(clause_no, '.', '')) >= num - 1
    UNION ALL
    SELECT dig.id, clause_no, num
    FROM design_input_general dig
    LEFT JOIN di ON di.model_id = '13' AND di.design_input_general_id = dig.id
    INNER JOIN (SELECT 1 num UNION SELECT 2 UNION SELECT 3
          UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) numbers 
        ON LENGTH(clause_no) - LENGTH(REPLACE(clause_no, '.', '')) >= num - 1
) t 
GROUP BY id, clause_no
ORDER BY
    GROUP_CONCAT(CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(clause_no, '.', num), '.', -1) REGEXP '[^0-9]'
                      THEN RPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(clause_no, '.', num), '.', -1), 8, 0) 
                      ELSE LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(clause_no, '.', num), '.', -1), 8, 0) 
                 END ORDER BY num SEPARATOR '');