CODEWITHSUNDEEP
mysql
David
David

Reputation: 1144

GROUP_CONCAT Sort by Integer, not String

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

Upvotes: 0

Views: 1630

Answers (1)

AbcAeffchen
AbcAeffchen

Reputation: 14987

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

Upvotes: 1

Related Questions