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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Both Easy and Powerful How easy is PHP?  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  Very powerful.  But a…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

773 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