CODEWITHSUNDEEP
mysqlsql
Clinton Lam
Clinton Lam

Reputation: 727

MySQL ORDER BY field created by GROUP_CONCAT()

Input two table, TABLE_A and TABLE_B

 TABLE_A       TABLE_B
 A_ID |   A    A_ID |  B        
    1 |   a       1 |  b
    2 |  a1       1 | b1
    3 |  a2       2 | b2

Expecting Output TABLE C

 TABLE_C
 A_ID |  A | C
    3 | a2 | NULL  <--- NULL if no matched A_ID in TABLE B
    1 |  a | b,b1  <--- Concat all rows in TABLE B with ','
    2 | a1 | b2

Following code can almost give the above TABLE_C except I want to sort field C with NULL first then DESC. ORDER BY C IS NULL DESC does not seems to work. Note that if C is NULL TABLE_C will order by A_ID regardless the value in field C.

SELECT 
  A1.A_ID, 
  A1.A, 
  GROUP_CONCAT(B1.B SEPARATOR ',') as 'C'
FROM `TABLE_A` A1 
  LEFT JOIN `TABLE_B` B1 
  ON A1.A_ID=B1.A_ID 
GROUP BY A1.A_ID, A1.A;

Following SQL gives error.

SELECT 
  A1.A_ID, 
  A1.A, 
  GROUP_CONCAT(B1.B SEPARATOR ',') as 'C'
FROM `TABLE_A` A1 
  LEFT JOIN `TABLE_B` B1 
  ON A1.A_ID=B1.A_ID 
GROUP BY A1.A_ID, A1.A
ORDER BY C IS NULL DESC, A1.A_ID; <--- Order by C with NULL failed.

Reference 'C' not supported (reference to group function)

Upvotes: 0

Views: 117

Answers (2)

tyro
tyro

Reputation: 1428

To ORDER BY Null values first and then by A1.A_ID use:

SELECT A1.A_ID, A1.A, GROUP_CONCAT(B1.B SEPARATOR ',') as C 
FROM `TABLE_A` A1 
LEFT JOIN `TABLE_B` B1 ON A1.A_ID=B1.A_ID 
GROUP BY A1.A_ID, A1.A 
ORDER BY (CASE WHEN GROUP_CONCAT(B1.B SEPARATOR ',') IS NULL then GROUP_CONCAT(B1.B SEPARATOR ',') ELSE A1.A_ID END) ;

and regarding the error Reference 'C' not supported (reference to group function) you should be ordering by 'C' ie 

ORDER BY 'C' IS NULL DESC, A1.A_ID;

and not ORDER BY C IS NULL DESC, A1.A_ID;

Upvotes: 1

Yahya Mukhtar
Yahya Mukhtar

Reputation: 474

You can not use Is Null in order by 

SELECT 
  A1.A_ID, 
  A1.A, 
  GROUP_CONCAT(B1.B SEPARATOR ',') as 'C'
FROM `TABLE_A` A1 
  LEFT JOIN `TABLE_B` B1 
  ON A1.A_ID=B1.A_ID 
GROUP BY A1.A_ID, A1.A
ORDER BY C, A1.A_ID DESC

Upvotes: 0

Related Questions