CODEWITHSUNDEEP
mysql
Vijay
Vijay

Reputation: 5433

concat the output of the subquery?

i have a query which would return values but i need them as a single output separated by commas..

So i tried to concat the output with the comma but it didn't work? 

select id from videos where duration=0;  /// this would return some rows

I tried concat and concat_ws but didn't work

select concat(select concat(id,',') from videos where duration=0);
select concat((select id from videos where duration=0),',');
select concat_ws(',',(select id from videos where duration=0));

i need the id's of all rows with the comma separtor

for example the output should be 1,4,6,78,565

any ideas?

Upvotes: 38

Views: 68333

Answers (4)

Omn
Omn

Reputation: 3070

To work around the inability to use LIMIT with GROUP_CONCAT you can group by the results of a sub query

This query breaks up your video ids into groups of 20 (but it could be REALLY slow on some datasets)

select group_concat(v.id) as video_list
from videos as v
where v.duration=0
group by (
    select floor(count(v2.id)/20)
    from videos as v2
    where v2.duration=0
    and v2.id <= v.id
)

Alternatively, if you have a lot of results but don't want such a slow query, you can increase to the size of your group_concat_max_len (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_group_concat_max_len) up to your max_allowed_packet (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_allowed_packet), if this is still not long enough, you would have to increase the max_allowed_packet size as well.

Upvotes: 0

Chinmayee G
Chinmayee G

Reputation: 8117

Try using GROUP_CONCAT

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

Reference: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Upvotes: 11

Julien Hoarau
Julien Hoarau

Reputation: 49970

Use group_concat :

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. 

SELECT
  GROUP_CONCAT(id)
FROM
  videos
WHERE
  duration=0

Upvotes: 8

Martin Smith
Martin Smith

Reputation: 453037

This is what group_concat does.

select group_concat(id) as video_list
from videos 
where duration=0

Upvotes: 56

Related Questions