GROUP_CONCAT Sort by Integer, not String

Question

Is it possible to sort elements from group_concat as integer values, not string values?

The order result is 1,11,12,3,5,6..
I Need 1,3,5,6,11,12...

I try with cast, but des not works:

GROUP_CONCAT( .. 
  ORDER BY 
   IF(attribute_value_order="order",
      CAST(value_order AS SIGNED),
           value_name) 
  ASC SEPARATOR "|" ) as value

I solve this with a LPAD, but is normal this behavior? or I do something wrong? — David
– David, Commented Aug 14, 2014 at 9:51

AbcAeffchen · Accepted Answer · 2014-09-23 23:58:10Z

1

Just running a test:

The table:

CREATE TABLE IF NOT EXISTS `gc` (
  `ord` int(11) NOT NULL,
  `group` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `gc` (`ord`, `gr`) VALUES
(1, 0), (2, 0), (1, 0), (2, 0), (11, 0), (12, 0), (3, 0), (5, 0),
(1, 1), (2, 1), (1, 1), (2, 1), (11, 1), (12, 1), (3, 1), (5, 1);

I ran SELECT GROUP_CONCAT(ord ORDER BY ord) FROM gc GROUP BY gr and the result isjust as you want it:

1,1,2,2,3,5,11,12

But if you use VARCHAR for the ord column, the result for the exact same query is

1,1,11,12,2,2,3,5

But The Query SELECT GROUP_CONCAT(ord ORDER BY CAST(ord AS SIGNED)) FROM gc GROUP BY gr returns again

1,1,2,2,3,5,11,12

I tested this with MySQL 5.6.11

UPDATE

You cannot change the ORDER BY clause in a GROUP_CONCAT with an IF, but you can change the whole GROUP_CONCAT for different groups by writing the IF in front of the GROUP_CONCAT

SELECT 
  gr, 
  IF(gr = 0, 
     GROUP_CONCAT(ord ORDER BY CAST(ord AS SIGNED)),
     GROUP_CONCAT(ord ORDER BY ord ) ) AS res
FROM gc 
GROUP BY gr

results in

gr  res     
0   1,1,2,2,3,5,11,12
1   1,1,11,12,2,2,3,5

edited Sep 23, 2014 at 23:58

answered Aug 14, 2014 at 15:34

AbcAeffchen

15.1k15 gold badges52 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Over a year ago

Try with an IF inside GROUP_CONCAT. My query is only a simple example, I have big query that join attributes, values and products. The values of atributes of some products. Attribute entity have a field with the order field of they values (name or order numeric). Depending of the attribute the values are sorted form one or other field. I doesn't have problems to do this with other system, but I was curious if this problem could solve.

AbcAeffchen Over a year ago

Maybe you can write yout if before the Group_concat. The problem seems to be, that you cannot change the order for every item that will be concatenated. But since your IF is not effected by the items you concat, you can use IF(..., GROUP_CONCAT(...), GROUP_CONCAT(...)) AS groupconcat.

Collectives™ on Stack Overflow

GROUP_CONCAT Sort by Integer, not String

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related