CODEWITHSUNDEEP
mysqlsql-order-by
Diego Flores
Diego Flores

Reputation: 41

Sequential order by in MySQL

I'm trying to realize the solution for my problem, but I can't find a way:I have a user/e-mail/domain table in MySQL and I would like to select and order by domain field such as the result would give me a sequential list, one domain at a time.

In example: let's say that my full table is 10 users with domain @gmail.com; 10 users with domain @aol.com and 5 users with domain @hotmail.com. The ordered result to my SELECT name, domain FROM mytable ORDER BY ??? should be:

user_x, aol.com
user_z, gmail.com
user_y, hotmail.com
user_a, aol.com
user_c, gmail.com
user_i, hotmail.com
...  when my five @hotmail.com records finished 
user_p, aol.com
user_j, gmail.com
user_t, aol.com
user_f, gmail.com
...

And so on.

Anyone have a clue on how to get this ordering?

Upvotes: 1

Views: 347

Answers (1)

spencer7593
spencer7593

Reputation: 108370

There's one approach I can think of that will achieve this in a MySQL query. But it's not pretty, and it relies on a behavior of user-defined variables which is documented to be undefined.

Consider this. If we had a set of rows like this: 

user    domain      seq_
------  ------      ----
user_a  aol.com        1
user_p  aol.com        2
user_t  aol.com        3
user_x  aol.com        4
user_c  gmail.com      1
user_f  gmail.com      2
user_j  gmail.com      3
user_z  gmail.com      4
user_y  hotmail.com    1
user_i  hotmail.com    2

Then we could do an ORDER BY seq_, domain, user to achieve the specified result.

The trick is getting the values for that seq_ column.

We can do something like this: order rows by domain, and then process the rows in order. If we save the value of domain from the current row into a user-defined variable, then we can use that for the next row, to do a comparison (comparing the value from the current row, with the value saved from the previous row.) If the value of domain is the same, we increment seq_, else, we reset seq_ to 1.

As I said, it's not pretty:

SELECT d.user
     , d.domain
  FROM ( SELECT @seq    := IF(t.domain = @domain, @seq + 1, 1) AS seq_
              , @domain := t.domain                            AS domain
              , t.user                                         AS user
           FROM ( SELECT @domain = '', @seq := 0 ) i
          CROSS
           JOIN mytable t
          ORDER
             BY t.domain
              , t.user
       ) d
 ORDER 
    BY d.seq_
     , d.domain
     , d.user

Again, this relies on behavior of user-defined variables which is not guaranteed. But with carefully crafted queries, we observe consistent behavior with MySQL 5.1, 5.5 and 5.6.

Upvotes: 1

Related Questions