Clever Geek Handbook

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?

0


source to share


2 answers


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

.

+1


source


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;
0


source






More articles:


All Articles