How to hack mysql group concat to fetch a limited number of rows


 

Limit record in group_concat + mysql

SELECT SUBSTRING_INDEX(GROUP_CONCAT(Field1 SEPARATOR ‘,’), ‘,’, [# of elements to return]) FROM TABLE;

Below SQL Query is for fetch subcategory value in a single field using “GROUP_CONCAT”.
if you want fetch limited no. of record then you can use

SUBSTRING_INDEX(GROUP_CONCAT(Field1 SEPARATOR ','), ',', 
  [# of elements to return])

SQL Query for display 4 subcategory list in a single query :

SELECT a.*, 
SUBSTRING_INDEX(GROUP_CONCAT(cast( b.uid AS char )), ',',4)
   as subcategories,
SUBSTRING_INDEX(GROUP_CONCAT(b.title SEPARATOR '#'), '#',4) 
  as subcategories_name
FROM `tx_jsproduct_category` AS a
LEFT JOIN `tx_jsproduct_category` AS b ON b.parent_id = a.uid
WHERE a.deleted =0
AND a.hidden =0
AND b.deleted =0
AND b.hidden =0
AND a.uid IN ( 1, 3, 2, 4  )
GROUP BY (
a.uid
)
ORDER BY  field( a.uid, 1, 3, 2, 4 )
LIMIT 0 , 30

Note:

SUBSTRING_INDEX(GROUP_CONCAT(b.uid SEPARATOR ','), ',',4) as 
  subcategories

4 is stand for limited record

Best Luck 🙂

If you have any query then feel free to contact me at Jainish Senjaliya

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s