我有以下表和数据(PostgreSQL):

我想要查询每个设备最新的f_count_at的一条记录,我们可以使用row_number() over(partition by)来实现:

1
SELECT * FROM (SELECT f_device_id, f_num, row_number() over(partition by f_device_id order by f_count_at desc) as rn FROM table) t WHERE t.rn = 1;

结果如下: