The function group_concat in MySQL will let you combine the values from multiple rows together in an aggregate function.

It’s a little hard to picture, so here’s a hypothetical aggregate query from the Netflix prize dataset:

select movie_id, customer_id, max(date) as latest, group_concat(rating) as all_ratings, count(*) as total_ratings
from ratings
group by movie_id,customer_id
having count(*)>1;

And we get back* **:

+----------+-------------+------------+-------------+---------------+
| MOVIE_ID | CUSTOMER_ID | LATEST     | ALL_RATINGS | TOTAL_RATINGS |
+----------+-------------+------------+-------------+---------------+
| 1        | 5           | 2007-12-02 | 3,2,4,5     | 4             |
| 54       | 3           | 2007-12-04 | 1,3,1       | 3             |
| 23       | 34          | 2007-12-07 | 5,5         | 2             |
+----------+-------------+------------+-------------+---------------+

As you can see for each set of rows grouped by the same movie_id and customer_id, it took each individual rating value and concatenated them all together in the new aggregate row.

* My actual query returned 0 rows so I thought I’d make up a quick example to illustrate the point.
** I made that pretty table*** with this ASCII table generating utility. Yay!
*** Pretty table may not be pretty in RSS readers or Blackberries.

Posted by Greg Pinero (Primary Searcher) as SQL at 9:49 PM MST

No Comments »

So I’m on Ubuntu 8.04 64bit and MySQL 5.0.51a. Today I decided I wanted to use InnoDB tables. But MySQL Administrator told me that InnoDB is not enabled currently.

So I first went into /etc/mysql/my.cnf and removed the skip-innodb variable, and added a few other performance oriented settings for using InnoDB tables and restarted MySQL. But alas it still wouldn’t work.

When I tried to create a table MySQL told me “Warning: Using storage engine MyISAM for table …”

and my log had all of this weird stuff in it:

mysqld[22162]: 080516 13:30:21 [Warning] Changed limits: max_open_files: 1024  max_connections: 100  table_cache: 457
mysqld[22162]: InnoDB: Error: data file /var/lib/mysql/ibdata1 is of a different size
mysqld[22162]: InnoDB: 640 pages (rounded down to MB)
mysqld[22162]: InnoDB: than specified in the .cnf file 128000 pages!
mysqld[22162]: InnoDB: Could not open or create data files.
mysqld[22162]: InnoDB: If you tried to add new data files, and it failed here,
mysqld[22162]: InnoDB: you should now edit innodb_data_file_path in my.cnf back
mysqld[22162]: InnoDB: to what it was, and remove the new ibdata files InnoDB created
mysqld[22162]: InnoDB: in this failed attempt. InnoDB only wrote those files full of
mysqld[22162]: InnoDB: zeros, but did not yet use them in any way. But be careful: do not
mysqld[22162]: InnoDB: remove old data files which contain your precious data!

So I tried restarting MySQL again and saw these weird log messages:

mysqld[22396]: 080516 13:37:19 [Warning] Changed limits: max_open_files: 1024  max_connections: 100  table_cache: 457
mysqld[22396]: InnoDB: Error: log file /var/lib/mysql/ib_logfile0 is of different size 0 5242880 bytes
mysqld[22396]: InnoDB: than specified in the .cnf file 0 524288000 bytes!

Finally out of desperation I tried following the advice in the log files above “… and remove the new ibdata files InnoDB created in this failed attempt”.

So I went into /var/lib/mysql and deleted these three files: ibdata1, ib_logfile0, and ib_logfile1. Then I restarted MySQL. It failed to start but I restarted again and everything worked!

Yay! I can now use InnoDB files like a professional.

Posted by Greg Pinero (Primary Searcher) as Ubuntu, SQL at 1:28 PM MST

Comments Off

sudo /etc/init.d/mysql restart

Posted by Greg Pinero (Primary Searcher) as Ubuntu, SQL at 10:11 AM MST

Comments Off

I wanted to do a union in order to insert a row at the beginning of the results for a query like so

select 'ALL' as id, 'All' as name
union
select owner as id, owner as name
from
account
order by name;

However it gave me this error:

Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'UNION'

So I tried using the collate clause to make everything the same:

select 'ALL' COLLATE latin1_swedish_ci as id,'All' COLLATE latin1_swedish_ci as name
union
select owner as id, owner as name
from
account
order by name;

But then I got this error:

COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'utf8'

So it turns out to fix this you have to set the character set and the collation in your select statement like so:

select _latin1 'ALL' COLLATE latin1_swedish_ci as id,_latin1 'All' COLLATE latin1_swedish_ci as name
union
select owner as id, owner as name
from
account
order by name;

Posted by Greg Pinero (Primary Searcher) as SQL at 7:59 AM MST

2 Comments »

In MS SQL Server a plain join without specifying inner or outer, etc results in an inner join.

Example:

select * from books join authors on books.authorid=authors.id

is equivalent to:

select * from books inner join authors on books.authorid=authors.id

Please leave comments if you know how this works in MySQL, Oracle, and other database systems. I’m trying to collect them all !

Posted by Greg Pinero (Primary Searcher) as SQL at 1:20 PM MST

2 Comments »