Limiting array / group _concat in MySQL
Let's say I have a table:
ij --- a 1 a 2 a 3 a 4 b 5 b 6 b 7 b 8 b 9
Obvoiusly SELECT a, GROUP_CONCAT(b SEPARATOR ',') GROUP BY a
will give me
a 1,2,3,4 b 5.6.7.8.9
But what if I only want a LIMITED number of results, like 2, like this:
a 1.2 b 5.6
Any ideas?
The best way is to use SUBSTRING_INDEX()
and GROUP_CONCAT()
.
SELECT i, SUBSTRING_INDEX( GROUP_CONCAT(j), ',', 2)
FROM mytable
GROUP BY i;
You don't need to know the field length here j
.
One solution would be to limit the rows in the group to the top two before calculating the aggregate.
SELECT t.i, GROUP_CONCAT(t.j)
FROM
(SELECT t1.i, t1.j
FROM mytable AS t1
LEFT JOIN mytable AS t2
ON (t1.i = t2.i AND t1.j >= t2.j)
GROUP BY t1.i, t1.j
HAVING COUNT(*) <= 2) AS t
GROUP BY t.i;
Another solution, if you know that the values in j
are of fixed length, is to simply use SUBSTRING()
for the result:
SELECT i, SUBSTRING( GROUP_CONCAT(j), 1, 3 )
FROM mytable
GROUP BY i;