MySQL: WHERE in a GROUP_CONCAT clause

Question

I've the following table:

table
+--------------------+-------+
| id      |  name    |  age  |
+--------------------+-------+
| 1       | client1  |  10   |
| 2       | client2  |  20   |
| 3       | client3  |  30   |
| 4       | client4  |  40   |
+--------------------+-------+

I'm trying to run a quest that would return the id, age of the first row and a comma delimited string of ages of all rows except the first.

So the output of the query should give:

4, 40, client4, "3,2,1"

I try to use GROUP_CONCAT in the following way:

SELECT id, age, name, SUBSTRING(GROUP_CONCAT(id), POSITION("," IN GROUP_CONCAT(id ORDER BY id DESC))+1)  as previous_ids
FROM table 
ORDER BY id DESC;

Query results:

1, 1, client1, "3,2,1"

It completely ignores the outer ORDER BY.

Any idea how to fix this?? or perhaps a different approach..

Thank you!

Answer 1

It completely ignores the outer ORDER BY

erm no.

Your query doesn't make a lot of sense as you are trying to aggregate records without specifying how they should be aggregated (no GROUP BY). And you should also be excluding id from the output EXCEPT where it is used within an aggregate function (sum, max, group_concat....).

Once you've untangled all that, your query still isn't going to what you intend because the ORDER BY at the end has no relevance to the order in which the GROUP_CONCAT() values are sorted. You have 2 GROUP_CONCAT functions here, and only one of them has its own ORDER BY clause.

Finally, using the 2 GROUP_CONCAT expressions is somewhat cumbersome. I would have gone with something like...

SELECT ilv.age
, SUBSTRING(ilv.records, POSITION("," IN ilv.records)+1) AS previous
FROM (
  SELECT age
  , GROUP_CONCAT(id ORDER BY id) AS records
  FROM table 
  GROUP BY age
) AS ilv;