postgresql 分组取前5条记录

select *
from (select id, name, state, amount_total, row_number() over (partition by state order by amount_total) as row_num
      from sale_order
     ) as a
where a.row_num <= 5;
 

除了row_number,类似的还有

1. rank() over()是跳跃排序，有两个第二名时接下来就是第四名
2. dense_rank() over()是连续排序，有两个第二名时仍然跟着第三名.