CODEWITHSUNDEEP
mysqlsql-order-bygroup-concat
H. Ferrence
H. Ferrence

Reputation: 8106

MySQL How to order a Joined and Group_Concat column?

I have a table named table_B with a sample set as follows:

+-serviceID-+-priceID-+-processOrder-+
|    001    |   003   |      03      |
+-----------+---------+--------------+
|    001    |   024   |      02      |        
+-----------+---------+--------------+
|    001    |   001   |      01      |        
+-----------+---------+--------------+

Here is my query statement:

$query_string = '
  SELECT services.*, GROUP_CONCAT(prices.priceID) AS priceID
  FROM table_A AS services
  INNER JOIN table_B AS prices ON services.serviceID = prices.serviceID
  WHERE services.serviceID = "' . $serviceID . '"
  GROUP BY prices.serviceID
  LIMIT 1
';
$result = mysqli_query($link, $query_string);

The result for $row['priceID'] is string(11) "001,003,024"

How can I order the resulting string in $row['priceID'] according to the processOrder column?

I am trying to return $row['priceID'] as string(11) "001,024,003"

Upvotes: 0

Views: 42

Answers (1)

zealot
zealot

Reputation: 247

Have a look at GROUP_CONCAT syntax:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
         [ORDER BY {unsigned_integer | col_name | expr}
             [ASC | DESC] [,col_name ...]]
         [SEPARATOR str_val])

you can set ordering inside parentheses.

Upvotes: 2

Related Questions