Sequential ordering from a mysql query

Question

I have a table of products that are associated to a retailer_id in a MySQL database and I would like to do a search on that table by a keyword on the title field but I would like to return the items in a sequential pattern using the retailer_id.

Pulling my hair out with this one but basically what I want to do is the following:-

title	retailer_id
red dress	1
red dress	1
red dress	2
red dress	3
red dress	5
red dress	4
red dress	4
red dress	3
red dress	3
red dress	4

I want to search for red dress which will show all results but then I want to sequentially order by retailer_id so the final output would be:-

title	retailer_id
red dress	1
red dress	2
red dress	3
red dress	4
red dress	5
red dress	1
red dress	3
red dress	4
red dress	3
red dress	4

Thanks in advance for any help

Answer 1

Using row_number you can rank each retailer_Id and order like so:

select retailer_id 
from (
    select *, Row_Number() over(partition by retailer_id order by retailer_id) rnk
    from t
)t
order by rnk, retailer_id;