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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

821 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