Custom MySQL Ordering

Question

I have a query that needs a custom sorting, trimmed down to the bare minimums something like:

SELECT u.*, p.*, p.id as product_id
FROM users u, products p 
WHERE u.id = p.user_id
ORDER BY product_id DESC

And I get returned a set of rows like:

UserID        ProductID
     2                5
     2                4
     3                3
     1                2
     1                1

But I want it to actually sort SOMETHING like this (so no 2 UserIDs are adjacent to eachother):

 UserID        ProductID
     1                2
     2                4
     3                3
     2                5
     1                1

Is this even possible with MySQL, or do I need some PHP magic?

Answer 1

A canonical way of solving this problem is by enumerating the duplicate rows and then ordering by that value:

select t.*
from (SELECT u.*, p.*, p.id as product_id,
             row_number() over (partition by u.id order by (select NULL)) as seqnum
      FROM users u join
           products p 
           on u.id = p.user_id
     ) t
order by seqnum, id;

This will work, as long as no one user has a really long sequence (as in your example).

There is no "always-works" solution, because it is easy to come up with a situation where your goal is not possible.