group_concat in MySQL

Murali · #21 (**permalink**) 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 (**permalink**) 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 (**permalink**) 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 (**permalink**) 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 (**permalink**) 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 (**permalink**) 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 (**permalink**) 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 (**permalink**) 08-16-2007, 11:20 PM

An useful information thanks Jeyaseelan.

Murali · #29 (**permalink**) 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 (**permalink**) 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

Murali · #31 (**permalink**) 08-20-2007, 10:43 PM

ORDER BY Clause with GROUP_CONCAT()

Code:

CREATE TABLE members (ID INTEGER PRIMARY KEY,names VARCHAR(100));

INSERT INTO members VALUES (1,'Ragavan');

INSERT INTO members VALUES (2,'Rajesh');

INSERT INTO members VALUES (3,'Rahul');

SELECT CAST(GROUP_CONCAT(ID ORDER BY ID ASC)AS BINARY)AS Id FROM members;

Code:

mysql> SELECT CAST(GROUP_CONCAT(ID ORDER BY ID ASC)AS BINARY)AS Id FROM members;                                              
+-------+
| Id    |
+-------+
| 1,2,3 |
+-------+
1 row in set (0.00 sec)

SELECT CAST(GROUP_CONCAT(ID ORDER BY ID DESC)AS BINARY)AS Id FROM members;

Code:

mysql> SELECT CAST(GROUP_CONCAT(ID ORDER BY ID DESC)AS BINARY)AS Id FROM members;
+-------+
| Id    |
+-------+
| 3,2,1 |
+-------+
1 row in set (0.00 sec)

Murali · #32 (**permalink**) 08-22-2007, 12:50 AM

Changing the Seperator in the retrieved values using GROUP_CONCAT()

Code:

mysql> SELECT CAST(GROUP_CONCAT(ID SEPARATOR '|')AS BINARY)AS Id FROM members;  
+-------+
| Id    |
+-------+
| 1|2|3 |
+-------+
1 row in set (0.00 sec)

prasath · #33 (**permalink**) 08-22-2007, 07:42 AM

Hi,
We can get the single record or more than record from a table.group_concat return or display all the records in single row.the give below query describes it

SELECT CAST(GROUP_CONCAT(CONCAT(acct_id,","),CONCAT(acct_ name,','),CONCAT(acct_affiliate_id,','),CONCAT(acc t_no,','),acct_password ORDER BY acct_id SEPARATOR '?')AS BINARY) FROM account

Prasath.K

Booom · #34 (**permalink**) 08-22-2007, 07:48 AM

ive created a sep thread for discussion on the performance of the top threads of discussweb at http://www.discussweb.com/discussweb...iscussweb.html
and ive move our posts to that thread.

Thanks

Murali · #35 (**permalink**) 08-22-2007, 10:02 PM

Quote:

Originally Posted by prasath

Hi,
We can get the single record or more than record from a table.group_concat return or display all the records in single row.the give below query describes it

SELECT CAST(GROUP_CONCAT(CONCAT(acct_id,","),CONCAT(acct_ name,','),CONCAT(acct_affiliate_id,','),CONCAT(acc t_no,','),acct_password ORDER BY acct_id SEPARATOR '?')AS BINARY) FROM account

Prasath.K

Really a good one , i tried it out and get the results corresponding column seperated with ? in a single row.

Murali · #36 (**permalink**) 08-22-2007, 10:16 PM

GROUP_CONCAT() Simplifies the code in PHP

Normal Query from table in PHP

PHP Code:

  <?PHP

$Sql = $mysqli-> query("SELECT ID,names FROM members");

while ($Record = $Sql->fetch_array(MYSQLI_ASSOC)) 

{

 $Result[] = $Record['names'];

}

$Sql -> close();

?>

A Simple Query using MYSQL GROUP_CONCAT() Function

Method1:

PHP Code:

  <?php

$Sql = $mysqli-> query("SELECT GROUP_CONCAT(names)AS Member_Name FROM members");

$Record = $Sql-> fetch_array(MYSQLI_ASSOC);

$Result = explode(',', $Record['Member_Name']);

$Sql -> close();

?>

Method2:

Here the result from the GROUP_CONCAT can be used with IN operator to get the results from an other table

PHP Code:

  <?php

$Sql = $mysqli-> query("SELECT GROUP_CONCAT(ID)AS Member_Id FROM members");

$Record = $Sql-> fetch_array(MYSQLI_ASSOC);

$Result = $Record['Member_Id']; 

$Query=$mysqli->query("SELECT * FROM member_details WHERE id IN ($Result)");

$Sql -> close();

?>

Murali · #37 (**permalink**) 08-23-2007, 05:24 AM

GROUP_CONCAT() Eliminates NULL values from the Field

For EX:

Consider this,

Code:

DROP TABLE IF EXISTS members;
CREATE TABLE members (ID INTEGER PRIMARY KEY,names VARCHAR(100));
INSERT INTO members VALUES (1,'Ragavan');
INSERT INTO members VALUES (2,'Rajesh');
INSERT INTO members VALUES (3,'Rahul');
INSERT INTO members VALUES (4,NULL);
INSERT INTO members VALUES (5,'Rohit');

Code:

mysql> SELECT * FROM members;
+----+---------+
| ID | names   |
+----+---------+
|  1 | Ragavan |
|  2 | Rajesh  |
|  3 | Rahul   |
|  4 | NULL    |
|  5 | Rohit   |
+----+---------+
5 rows in set (0.00 sec)

Code:

mysql> SELECT CAST(GROUP_CONCAT(ID)AS BINARY),GROUP_CONCAT(names) FROM members;
+---------------------------------+----------------------------+
| CAST(GROUP_CONCAT(ID)AS BINARY) | GROUP_CONCAT(names)        |
+---------------------------------+----------------------------+
| 1,2,3,4,5                       | Ragavan,Rajesh,Rahul,Rohit |
+---------------------------------+----------------------------+
1 row in set (0.00 sec)

In the Result we just get the NON NULL values from the names Field,

Now how to identify the Id for the names Field which is NULL?

Here is the simple way,

Code:

mysql> SELECT CAST(GROUP_CONCAT(ID)AS BINARY),GROUP_CONCAT(IF(ISNULL(names)=1,'#',names)) FROM members;
+---------------------------------+---------------------------------------------+
| CAST(GROUP_CONCAT(ID)AS BINARY) | GROUP_CONCAT(IF(ISNULL(names)=1,'#',names)) |
+---------------------------------+---------------------------------------------+
| 1,2,3,4,5                       | Ragavan,Rajesh,Rahul,#,Rohit                |
+---------------------------------+---------------------------------------------+
1 row in set (0.00 sec)

Now in the result we get # instead of NULL value while processing get the # Value and replace it, even its easy for us to get the corresponding id for the name which is NULL.

Hope got it clear !!!

Murali · #38 (**permalink**) 08-24-2007, 11:10 PM

GROUP_CONCAT() with GROUP BY Expression

Code:

mysql> CREATE TABLE members_details (MemberDetailId INTEGER PRIMARY KEY,MemberId INTEGER NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO members_details VALUES (1,1),(2,1),(3,1),(4,2),(5,2),(6,2);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

Code:

mysql> SELECT MemberId,CAST(GROUP_CONCAT(MemberDetailId)AS BINARY) AS MemberDetailId FROM members_details GROUP BY MemberId;
+----------+----------------+
| MemberId | MemberDetailId |
+----------+----------------+
|        1 | 1,2,3          |
|        2 | 4,5,6          |
+----------+----------------+
2 rows in set (0.00 sec)

Murali · #39 (**permalink**) 08-30-2007, 11:15 PM

Quote:

Originally Posted by Murali

Really a good one , i tried it out and get the results corresponding column seperated with ? in a single row.

Here is an other simple way to get the same,

Code:

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

Code:

mysql> INSERT INTO members VALUES (1,'Ragavan'),(2,'Rajesh'),(3,'Rahul');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Code:

mysql> SELECT CAST(GROUP_CONCAT(CONCAT_WS(',',ID,names)ORDER BY ID SEPARATOR ',')AS BINARY) AS MemDetail FROM members;
+----------------------------+
| MemDetail                  |
+----------------------------+
| 1,Ragavan,2,Rajesh,3,Rahul |
+----------------------------+
1 row in set (0.00 sec)