MySQL ORDER BY rotating through values

Question

I have a a table containing a list of email addresses. I want to select a subset of the email addresses but I want the ORDER BY statement to rotate through the domain portion of the email address, feeding them every other one.

Example Table:

+----+-------------------+-------+
| id | email             | name  |
+----+-------------------+-------+
| 1  | bob@gmail.com     | Bob   | 
| 2  | tom@gmail.com     | Tom   |
| 3  | greg@gmail.com    | Greg  |
| 4  | lisa@gmail.com    | Lisa  |
| 5  | sally@yahoo.com   | Sally |
| 6  | aaron@yahoo.com   | Aaron |
| 7  | tim@yahoo.com     | Tim   |
| 8  | wanda@hotmail.com | Wanda |
| 9  | smith@hotmail.com | Smith |
| 10 | mark@hotmail.com  | Mark  |
+----+-------------------+-------+

I am parsing the domain portion of the email using substring_index(email, '@', -1).

I would run a query something like:

SELECT id, 
    email, 
    name 
FROM emails 
ORDER BY substring_index(email, '@', -1) 
    [something here to rotate every other result], 
    id ASC

And I would want the results to look like this:

+----+-------------------+-------+
| id | email             | name  |
+----+-------------------+-------+
| 1  | bob@gmail.com     | Bob   | 
| 8  | wanda@hotmail.com | Wanda |
| 5  | sally@yahoo.com   | Sally |
| 2  | tom@gmail.com     | Tom   |
| 9  | smith@hotmail.com | Smith |
| 6  | aaron@yahoo.com   | Aaron |
| 3  | greg@gmail.com    | Greg  |
| 10 | mark@hotmail.com  | Mark  |
| 7  | tim@yahoo.com     | Tim   |
| 4  | lisa@gmail.com    | Lisa  |
+----+-------------------+-------+

Is this something possible to iterate through an item during ORDER BY?

Any advice/insight would be GREATLY appreciated :)

This would almost certainly be easier to deal with in your application layer. — Nick, May 07 '20 at 00:49
This can be easily done in MySQL 8.x. Are you using MySQL 5.x or 8.x? — The Impaler, May 07 '20 at 02:13

score 1 · Answer 1 · answered May 07 '20 at 02:17

You don't mention which version of MySQL you are using, so I'll assyume it's a modern one (8.x).

You can do:

select id, email, name,
from (
  select id, email, name,
    substring_index(email, '@', -1) as dom,
    row_number() over(partition by substring_index(email, '@', -1) 
                      order by name) as rn
  from emails
) x
order by rn, dom

MySQL ORDER BY rotating through values

1 Answers1