MySQL ORDER BY id, but keep identical names next to each other Suppose I have the following table:

id  name
---------
1   lion
2   tiger
3   lion
4   bear
5   tiger
6   bear


I would like to be able to return results with the most recent id column first, but keep all of the names together, like this:

id  name
---------
6   bear
4   bear
5   tiger
2   tiger
3   lion
1   lion


Is there a simple ORDER BY clause that can do this? Or do I have to do a UNION of some type?
You need to create an ordering based on the maximum id for name. Here's one attempt:

select id, name 
from animals x 
order by (select max(id) from animals y where x.name = y.name) desc
    , id desc;


It might be easier to understand if the derived column is explicit, and then order by that:

select id, name 
from (
    select id, name
         , (select max(id) 
            from animals y 
            where x.name = y.name
           ) as max_id 
    from animals x 
) as t 
order by max_id desc, id desc;


You could also use a join to a derived table, instead of the correated subquery:

select x.id, x.name 
from animals x
    join
       (select name, max(id) as max_id 
        from animals
        group by name
       ) y 
    on x.name = y.name
order by y.max_id desc, x.id desc;