2

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?

2

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;
0
1

Here another possible solution.. using join

select t2.id,t2.name
from test t join test t2 on t2.name = t.name
group by t2.id order by max(t.id) desc, t2.id desc;

sqlfiddle : http://www.sqlfiddle.com/#!9/826272/23

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.