如何按列对分组元素进行排序 - How to order inside grouped elements by column?

How to order inside grouped elements by column?

本文关键字：排序元素何按列 | 更新日期: 2023-09-27

尝试订购这个:

|    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

这:

这段代码执行作业分组id:

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;

问题是created_at以相反的顺序出现，像这样:

如何在每个分组id块内反转created_at顺序?

为order by添加另一个键:

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, created_at desc;

select * from table order by id desc,created_at desc

所以你必须删除tt。maxca和添加created_at到你的分组:

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 id desc, created_at desc;