CODEWITHSUNDEEP
mysql
Mark Elvis
Mark Elvis

Reputation: 61

limiting the results of group_concat()

i have simple two tables 

     table1               table2
| id |    name    |    | id |    name    |
| 1  |    name_1  |    | 1  |    name-1  |
| 2  |    name_2  |    | 2  |    name-2  |
| 3  |    name_3  |    | 3  |    name-3  |
...................    ...................
     100 row                10000 row

i get the names out of these tables like this

select 
(
  select group_concat(name) from table1
) as t1
, 
(
  select group_concat(name) from table2
) as t2

but i want to limit the rows results to 50 row only

how to do that efficiently.

the results i get are like this

t1: name_1, name_2, name_3 . . . , name_100
t2: name-1, name-2, name-3 . . . , name-10000

but what i want is to stop at number 50.

Upvotes: 1

Views: 249

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

In a Derived table, first get only 50 rows. Now, you can use this result-set for Group_concat()

However, note this important point from the Docs:

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.

50 values will most likely generate a very long string (more than 1024 characters). Your result would be truncated if you don't increase the value of group_concat_max_len system variable before running your query:

-- increasing group_concat_max_len for this session
SET SESSION group_concat_max_len = @@max_allowed_packet;

SELECT GROUP_CONCAT(dt.name ORDER BY dt.id) 
FROM
( 
  SELECT id, name 
  FROM table1 
  ORDER BY id LIMIT 50
) AS dt

Upvotes: 4

Related Questions