group_concat in MySQL

Murali · #21 08-03-2007, 09:59 AM

Quote:

Originally Posted by Murali

Hi,

Have u noticed one thing,

Code:

SELECT GROUP_CONCAT(Field_name) FROM table_name LIMIT 0,10;

But didn't get the expected result?

Ya may be this is the reason,using this query
SELECT GROUP_CONCAT(Field_name) FROM table_name LIMIT 1,10;

i didn't get any output from the quey, it shows that the GROUP_CONCAT() Function captures the values from the field and then applies the LIMIT CLAUSE.

This may be reason....

Murali · #22 08-03-2007, 11:49 AM

Quote:

Originally Posted by priyan

Hi Murali,
Generaly group_concat functions returns the result in blob format. It seems like like toad error / may not a error also. Anyway, if you want to view the result set in toad using group_concat function means use CAST function and convert the group concat result set into binary.

Eg..
SELECT CAST(group_concat(emp_id) AS BINARY) FROM emp_master;

Hi Priyan,

Found a new one, check this out.

In MYSQL

mysql> SELECT myfield FROM test_cast;

Quote:

myfield
123
456
abcd
03 rows in set (0.00 sec)

SELECT CAST(GROUP_CONCAT(myfield)AS BINARY) FROM test_cast;

Quote:

Output: 123,456,abcd

In ORACLE

SELECT CAST('123'AS INTEGER) AS VAL FROM DUAL;

Quote:

Output:
VAL
----------
123
1 row selected.

SELECT CAST('123,ABCD'AS INTEGER) AS VAL FROM DUAL;

Quote:

Output:
ORA-01722: invalid number

Seems to be CAST Functionality differs with using GROUP_CONCAT() Function in MYSQL.

Murali · #23 08-07-2007, 06:10 AM

Could be this way

CAST AS INTEGER -- Converting a string to Integer.
CAST AS BINARY -- Converting a string to its Binary Value.

Am I rite?

Murali · #24 08-09-2007, 07:24 AM

Quote:

Originally Posted by Murali

Hi Priyan,

Found a new one, check this out.

In MYSQL

mysql> SELECT myfield FROM test_cast;

SELECT CAST(GROUP_CONCAT(myfield)AS BINARY) FROM test_cast;

In ORACLE

SELECT CAST('123'AS INTEGER) AS VAL FROM DUAL;

SELECT CAST('123,ABCD'AS INTEGER) AS VAL FROM DUAL;

Seems to be CAST Functionality differs with using GROUP_CONCAT() Function in MYSQL.

When i use

Code:

mysql> SELECT GROUP_CONCAT(CAST(MyTable_Id AS BINARY)),GROUP_CONCAT(CAST(MyTable_Value AS BINARY)) FROM MyTable;

This returns the recordset but with warnings !!!!!

Code:

 mysql> show warnings;
+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1260 | 1 line(s) were cut by GROUP_CONCAT() |
+---------+------+--------------------------------------+
1 row in set (0.00 sec)

Could i know is that any limitations for GROUP_CONCAT() and how to overcome it?

kumaresan · #25 08-14-2007, 04:25 AM

Hi,

In group_concat() function

SELECT GROUP_CONCAT(name) FROM employee LIMIT 2 -- returns all name

while using group_conact() function with limit clause retruns unexpected result .

so change the query like

SELECT GROUP_CONCAT(A.NAME) FROM (SELECT * FROM E_EMPLOYEE LIMIT 2) A -- returns only two name.

Thanks,
V.Kumaresan.

Murali · #26 08-14-2007, 07:06 AM

Hi Kumaresan,

Code:

SELECT GROUP_CONCAT(A.NAME) FROM (SELECT * FROM E_EMPLOYEE LIMIT 2) A -- returns only two name

Works Fine.....Thanks

Jeyaseelansarc · #27 08-16-2007, 10:42 PM

Quote:

Originally Posted by Murali

Hi,

Have u noticed one thing,

Code:

SELECT GROUP_CONCAT(Field_name) FROM table_name LIMIT 0,10;

But didn't get the expected result?

Hi Marali,

group_concat is getting only 4294967296(2^32) chars at the Maximum in a select query

Murali · #28 08-16-2007, 11:20 PM

An useful information thanks Jeyaseelan.

Murali · #29 08-16-2007, 11:30 PM

Eliminating the duplicates using GROUP_CONCAT()

Code:

mysql> CREATE TABLE members (ID INTEGER PRIMARY KEY,names VARCHAR(100));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO members VALUES (1,'Ragavan');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO members VALUES (2,'Ragavan~');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO members VALUES (3,'Ragavan');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO members VALUES (4,'Rajesh');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO members VALUES (5,'Rahul');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(names)FROM members;
+---------------------------------------+
| GROUP_CONCAT(names)                   |
+---------------------------------------+
| Ragavan,Ragavan~,Ragavan,Rajesh,Rahul |
+---------------------------------------+
1 row in set (0.00 sec)

-- Now getting the Distinct Names using GROUP_CONCAT()

mysql> SELECT GROUP_CONCAT(DISTINCT names)FROM members;
+-------------------------------+
| GROUP_CONCAT(DISTINCT names)  |
+-------------------------------+
| Ragavan,Ragavan~,Rajesh,Rahul |
+-------------------------------+
1 row in set (0.00 sec)

Hope this helps you all !!

kumaresan · #30 08-18-2007, 03:57 AM

hi,

If you want to handle group_concat() result with names
and want use the result in another query ,

consider the following table

CREATE TABLE employee (id int NOT NULL,
name varchar(40) ,
deptid int ,
salary bigint ,
address varchar ,
PRIMARY KEY (id) );

insert into employee values(1,'ram',10,20000,'vellore') ;
insert into employee values(2,'raja',10,10000 ,'chennai') ;

select group_concat(concat('''',name,''''))into @a from employee;

-- this query returns 'ram','raja' to @a

set @s:=concat('select * from employee where name in (',@a,');');
prepare stm1 from @s;
execute stm1 ;
deallocate prepare stm1;

RESULT
-------

id name deptid salary address
---------------------------------------------------
1 ram 10 20000 vellore
2 raja 10 10000 chennai

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
MySQL	lldcrb328	PHP Programming	2	02-15-2009 09:45 PM
I Can Help With MYSQL	theseokit	Database Support	9	01-21-2009 11:01 PM
php-mysql	lekshmy	PHP Programming	0	01-21-2009 10:58 PM
Why to use MySQL	Amisha_Sharma	Database Support	1	01-07-2009 07:56 PM
multiple group_concat problem	fncll	Database Support	4	11-30-2007 12:06 AM