postgres数据库分组排序后再取第一条数据

postgres数据库分组排序后再取第一条数据，最后再分页需求：先根据企业ID分组，然后按时间倒序，取每一组的第一条数据，最后再进行分页。select * from(select row_number()over(partition by company_id order by create_time desc) row_id,* from company_changewhere company_

utada hikki

4643人浏览 · 2021-12-23 12:05:22

utada hikki · 2021-12-23 12:05:22 发布

postgres数据库分组排序后再取第一条数据，最后再分页

需求：先根据企业ID分组，然后按时间倒序，取每一组的第一条数据，最后再进行分页。

select * from 
(select row_number()over(partition by company_id order by create_time desc) row_id,* from company_change 
where company_id in (437846971400192,440551493844992,437860660576256,440019449217024)
and  create_time >= '2021-12-21 17:14:21' and create_time <= '2021-12-23 09:47:45.814') as  company_change 
where row_id=1 limit 2 offset 0;

查询的结果：

不分页的时候，看change_number一目了然：

select * from
(select row_number()over(partition by company_id order by create_time desc) row_id,* from company_change
where company_id in (437846971400192,440551493844992,437860660576256,440019449217024)
and create_time >= '2021-12-21 17:14:21' and create_time <= '2021-12-23 09:47:45.814') as company_change
where row_id=1;