How to sort a MySQL table by email address domain

I have MySQL table that has an email address field, and I want to sort the table by the domain of the email address. I went looking for some complex substringy way of doing this when I came across this elegant solution: just sort by the reverse of the email address.

While this doesn’t actually sort the addresses so that, say, AOL addresses come first followed by Bellsouth, Comcast, Dartmouth, etc., it does group all the email addresses for the same domain together in order, which is all I really needed. If this is all you really need, then it’s as simple as:

select * from table order by reverse(email)

Comments

stan's picture
stan on June 12, 2008 - 01:14 Permalink

Hmm… how would that work? Would something like ma@aol.com vs. al@aol.com get sorted wrong?

moc.loa@am
moc.loa@la <= Sorted after ma

Cody Caughlan's picture
Cody Caughlan on June 12, 2008 - 03:00 Permalink

This is a feasible solution if you only have a small number of rows, as no index can be used. That is, even if you do have an index on the email column, MySQL will not be able to use it.

I think your best bet is to parse out the domain in the application and then store the domain alongside the email in the DB and then sort on that — if you index the domain column then the index can be used and you dont need to worry about calling a non-deterministic MySQL function on a column.

karthikeyan's picture
karthikeyan on June 12, 2008 - 06:25 Permalink

Good one. very concise and useful.

Daniel's picture
Daniel on June 12, 2008 - 07:57 Permalink

In sql server I would do something like

select * from table order by substring(email, charindex(‘@’, email) + 1, len(email)), email

this will order first by domain, then by email. I guess MySQL has similar functions

phoenix face's picture
phoenix face on June 12, 2008 - 15:38 Permalink

I think adding a new “domain_name” column is the right way. The email address validation routine should be able to parse out the domain name.

Joe Winett's picture
Joe Winett on November 24, 2009 - 20:56 Permalink

In MySQL you can do:

SELECT * FROM Users WHERE SUBSTR( userEmail, LOCATE(‘@’,userEmail))

But, of course, this requires a table scan. It’s not a big deal if you don’t have loads of rows.

Joe Winett's picture
Joe Winett on November 24, 2009 - 20:57 Permalink

Oops. Should have been: SELECT * FROM Users ORDER BY SUBSTR( userEmail, LOCATE(‘@’,userEmail))

Marco's picture
Marco on March 31, 2011 - 13:04 Permalink

Top Solution.

Just what I needed.

Thx

Andrew X's picture
Andrew X on April 1, 2011 - 05:33 Permalink

Folks, if you want to do this and also take advantage of indexing, just *store* the reversed email address, right from the beginning. Just be sure to give it a descriptive name, such as “reversed_email_address”.

While keeping an extra column just to store the (redundant) domain also allows indexing, it is wasteful of space.

Alternatively, if you don’t care about indexes (in which case, Shame on you!), and you don’t like storing the reversed email address, but still want to sort by domain (and with an actually correct lexicographical ordering), just do:

SELECT *, SUBSTRING( email_address, POSITION( “@” IN EMAIL_ADDRESS ) + 1 ) as domain FROM The_Table;