如何按一列排序，然后将同一列id的最新块推到顶部？

Question

For example I have table:

|    id    |    title    |    created_at    |

     1                            12:00           
     2                            13:00
     2                            14:00
     1                            15:00

I want same id numbers to be near each other. In this case 1 1 2 2 or 2 2 1 1 AND order same chunks of id's by created_at time so the chunk of id's which own the latest created_at stays on top, then goes one, having highest created_at compared to 3rd chunk of id's and so on. How do I do it?

orderBy('id', 'desc')->orderBy('created_at', 'desc')->get(); // orders id's to same id chunks, but it doesn't sort that the chunk with latest id chunk (1 1) created_at at the top.

orderBy('created_at', 'desc')->orderBy('id', 'desc')->get(); // gives the latest created_at at top and so on, but same id's arent close to each other.

Bigger example:

|    id    |    title    |    created_at    |

 1                            12:00  
 2                            13:00
 1                            15:00
 2                            15:00
 1                            17:00
 3                            18:00
 1                            19:00
 3                            20:00

Want to anchieve that foreach($table_rows as $row) { } would give me result:

3    20:00
3    18:00
1    19:00
1    17:00
1    15:00
1    12:00
2    15:00
2    13:00

I know it's hard to do with mysql alone. How do I do this in php easiest way?

I bet I have to sort by id first and then push each id's chunk relative to each other by latest created_at.

Answer 1

You need to get the information of the most recent date for each id. Here is a method using join and an aggregation:

select t.*
from table t join
     (select t.id, max(created_at) as maxca
      from table t
      group by t.id
     ) tt
     on t.id = tt.id
order by tt.maxca desc, id;

The rest is just the order by using the maximum value.

I don't know how to express this in laravel, but your question is also tagged mysql.