When GROUP BY just doesn't work

Recently I was doing a rankings application. Its main part were companies and their financial results. So we had a table like this:

CREATE TABLE companies(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  key INT,
  name VARCHAR(255),
  position INT,
  year INT
);

Every company had a few entries in this table, each with a different year, name and position but with the same key. What I had to do was to find the newest entry for each company. My first idea was to use GROUP BY(key), but as it turned out, GROUP BY can return anything from the grouped values, and that means it won’t necessarily be th newest entry. And the solution I found is quite clever:

SELECT Company.* FROM companies as Company 
  LEFT JOIN companies AS c ON Company.key = c.key 
  AND Company.year < c.year 
  WHERE c.key IS NULL 
  ORDER BY name

We’re joining companies table with itself on the condition that joined table’s year will be greater that the first table’s. So at some point we will get a row where there is no greater year, and we are returning that row. Great!

mysqldump

I just keep forgetting how to properly export data with utf8 charset, so this one’s for me:

mysqldump database table1 table2 table3 --default-character-set=utf8 -u user -ppassword [-h host] > file.sql