Mysql group_concat() alternative in 3.23.x

Posted on 2004-04-15
Last Modified: 2012-08-13
I need a functional alternative to the group_concat function since its not possible for me to upgrade to 4.1 at this time.

Im creating a temporary table that stores all keyword_ids of all relevant item_ids for that site, format is item_id is PRIMARY and keyword_ids is a comma seperated list of keyword ids (eg 1,2,3). Each item has sub-properties , each sub-property has its own record in the sub-property db and is related via an item_id col. in that sub-property db, the keyword_ids col. is in the sub-property db. I need to have keyword_ids be a list of all the keyword_ids for that item, the query I have now is working fine, however its returning multiple rows for each item_id.

id | item_id | keyword_ids
1  |1          | 1,2,3          
2  |1          | 1,2            
3  |1          | 1                
4  |1          | 4,5,6          
5  |2          | 7                
6  |2          | 1,4,2          
7  |2          | 6,3,4            

Im getting that by this...
CREATE TEMPORARY TABLE (id bigint(20) unsigned NOT NULL auto_increment,PRIMARY KEY  (id),KEY item_id (item_id)) TYPE=MyISAM SELECT DISTINCT item_id,keyword_ids FROM my_item_joined_db WHERE site_id = x

(my_item_joined_db = few left joins that grab the particular keyword_ids for the items on that partikcular section of the site).

What I need is to have that temp table only have one row per item_id (ie GROUP BY item_id) with all the keyword_ids for that item_id concat'ed together (ie for item_id 1 keyword_ids would be 1,2,3,1,2,1,4,5,6). I need to do this all via mysql, is there a way to do it via creating another temp table and dumping from another select, etc.? Thanks.
Question by:jcsuperska
  • 3
  • 2
LVL 14

Accepted Solution

psadac earned 500 total points
ID: 10847100
It works well on mysql 4.0.18 (it should work with 3.X versions also) :

SELECT @prev_item_id := NULL;

SELECT  MAX(@keyword_ids := IF(@prev_item_id = item_id, CONCAT_WS(',', @keyword_ids, keyword_ids), keyword_ids)),
             @prev_item_id := item_id
FROM mytable
GROUP BY item_id;

very standard sql, no ? ;-)

Author Comment

ID: 10861896
That sounds like it should work great, unfortunatly mysql is returning this error and any help with this would be greatly appreciated.

Incorrect column name 'MAX(@keyword_ids := IF(@prev_item_id = item_id, CONCAT_WS(',', @keyword_ids, keyword_ids), keyword_ids))'

That mean Im gonna need to upgrade to 4.0.18 or anything look obviously wrong?
LVL 14

Expert Comment

ID: 10863258
can you post your complete query and the error message ?

Author Comment

ID: 10863456
Query is:
SELECT @prev_item_id := NULL;CREATE TEMPORARY TABLE IF NOT EXISTS keyword_table_site_2 (id bigint(20) unsigned NOT NULL auto_increment,PRIMARY KEY (id),KEY item_id (item_id)) TYPE=MyISAM SELECT as item_id,MAX(@keyword_ids := IF(@prev_item_id = item_id, CONCAT_WS(',', @keyword_ids, keyword_ids), keyword_ids)),@prev_item_id := item_id FROM sub_items LEFT JOIN items ON sub_items.item_id = WHERE items.site_id = 2 AND items.site_date <= NOW() GROUP BY item_id

MySQL said:
Incorrect column name 'MAX(@keyword_ids := IF(@prev_item_id = item_id, CONCAT_WS(',', @keyword_ids, keyword_ids), keyword_ids))'

Thank you for your help.

Author Comment

ID: 10864666
Your solution as I thought was absolutly correct. Only issue was being caused by the fact that I was selecting the results into a TEMP table and the col names were not being aliased. So when I added as keyword_ids to the MAX(....) it worked like a charm. Always the simple things that seem to be the cause , thanks again for helping me get the group_concat() or bust thinking out of my head =), just had to get the wheels turning.

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Sending a Secure fax is easy with eFax Corporate ( First, just open a new email message. In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now