SQL, multiple email adresses problem

Question

I've a user table (MySQL) with the following data

id  email       creation_date
1   bob@mail.com    2011-08-01 09:00:00
2   bob@mail.com    2011-06-24 02:00:00
3   john@mail.com   2011-02-01 04:00:00
4   john@mail.com   2011-08-05 20:30:00
5   john@mail.com   2011-08-05 23:00:00
6   jill@mail.com   2011-08-01 00:00:00

As you can see we allow email duplicates so its possible to register several accounts with the same email address. Now I need to select all adresses ordered by the creation_date but no duplicates. This is easy (i think)

SELECT * FROM (SELECT * FROM users ORDER BY creation_date) AS X GROUP BY email

Expected result:

id  email       creation_date
2   bob@mail.com    2011-06-24 02:00:00
6   jill@mail.com   2011-08-01 00:00:00
3   john@mail.com   2011-02-01 04:00:00

But then I also need to select all other adresses, ie. all that are not present in the result from the first query. Duplicate are allowed here.

Expected result:

id  email       creation_date
1   bob@mail.com    2011-08-01 09:00:00
4   john@mail.com   2011-08-05 20:30:00
5   john@mail.com   2011-08-05 23:00:00

Any ideas? Perfomance is important because the real database is very huge

Answer 1

SELECT * FROM a
FROM users a
LEFT JOIN (SELECT email, MIN(creation_date) as min_date GROUP BY email)x ON 
 (x.email = a.email AND x.min_date=a.creation_date)
WHERE x.email IS NULL

Answer 2

In SQL server we would do a Select statement using a rank.

Here are some MYSQL samples: How to perform grouped ranking in MySQL

http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query/

I hope this helps.