You can chanage separator as follows: mysql> SELECT *, -> GROUP_CONCAT(DISTINCT test_score -> ORDER BY test_score DESC SEPARATOR ‘ |’) -> FROM table_name
Post: GROUP_CONCAT useful GROUP BY extension
September 4, 2006 By 43 Comments
MySQL has useful extention to the GROUP BY operation: function GROUP_CONCAT: GROUP_CONCAT(expr) – This function returns a string result with the concatenated … PHP: old way: with group_concat: This should work faster, as we remove loop from PHP to MySQL server side. Also it can…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, ‘.’, P.COLUMN_NAME, ‘ AS `’, P….REFERENCED_TABLE_NAME, ‘ ‘, ‘ ON (‘, GROUP_CONCAT(CONCAT(K.CONSTRAINT_NAME, ‘.’, K.COLUMN_NAME) ORDER BY K.ORDINAL_POSITION), ‘) = (‘, GROUP_CONCAT(CONCAT(K.REFERENCED_TABLE_NAME, ‘.’, K…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…constantly referring to the amazing MySQL manual, especially the option and variable reference table. But….commanual general_logblogpercona.commanual general_log_fileblogpercona.commanual group_concat_max_lenblogpercona.commanual Handler_commitblogpercona.commanual Handler_deleteblogpercona….
Post: Testing MySQL column stores
August 16, 2010 By 15 Comments
… first first part of my analysis focused on testing specific MySQL functionally with Infobright’s storage engine. A lot of my… edge cases since I have experience developing such cases for MySQL column-based storage engines in the past. I reported any… of the queries. ICE supports almost all of the MySQL aggregation functions. Notably, GROUP_CONCAT is not supported, which is something I hope…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
… there are lots of them: mysql> SELECT CONCAT_WS(‘.’, db, tbl, idx) AS idx, -> GROUP_CONCAT(alt_idx) AS alternatives, -> GROUP_CONCAT(DISTINCT query_id) AS queries, SUM(cnt) AS cnt -> FROM index_alternatives -> GROUP BY db…
Comment: GROUP_CONCAT useful GROUP BY extension
November 1, 2011 By 43 Comments
… you , Vadim Tkachenko i used group_concat in my query but i got i problem problem is mysql group_concat limit length is 1024 and… did it ) ( but is there is problem when i increase group_concat length to more than 1024 ) it work fine in localhost…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…N N** N*** Y *All aggregate functions supported, except GROUP_CONCAT and AVG(distinct). **non-deterministic functions like RAND(),… | 155187034 | <-- too high +------------------+ 1 row in set (0.68 sec) mysql> select sum(total_lines) from dashboard_customer_sales ; +——————+ |…
Comment: GROUP_CONCAT useful GROUP BY extension
September 6, 2006 By 43 Comments
GROUP_CONCAT in MySQL… GROUP_CONCAT(expr) – This function returns a string result with the concatenated non-NULL values from a group. Where it can be useful? For example to get PHP array without looping inside PHP: CREATE TABLE services ( id INT UNSIGNED NOT NULL, client_id …
Post: Researching your MySQL table sizes
March 17, 2008 By 26 Comments
… index size for given MySQL Instance SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2),’M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),’G') data, concat(round…_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES GROUP BY table_schema ORDER BY sum(data_length+index_length…
