从每个不同的值中选择5个 [英] Select 5 of each distinct value

问题描述

我在PostgreSQL中有下表:

I have the following table in PostgreSQL:

|  a  |  b  |  c  |
===================
| 'w' |  2  |  3  |
| 'w' |  7  |  2  |
| 'w' |  8  |  1  |
| 'w' |  3  |  6  |
| 'w' |  0  |  8  |
| 'w' |  2  |  9  |
| 'w' |  2  |  9  |
| 'z' |  4  |  9  |
| 'z' |  0  |  9  |
| 'z' |  0  |  8  |
| 'z' |  3  |  6  |
| 'z' |  2  |  7  |
| 'z' |  3  |  1  |
| 'z' |  3  |  2  |
| 'z' |  3  |  3  |

我想选择所有记录，但对于 a 列中的每个不同值，将它们限制为5条记录.
因此结果将如下所示:

I want to select all records, but limit them to 5 records for each distinct value in column a.
So the result would look like:

|  a  |  b  |  c  |
===================
| 'w' |  2  |  3  |
| 'w' |  7  |  2  |
| 'w' |  8  |  1  |
| 'w' |  3  |  6  |
| 'w' |  0  |  8  |
| 'z' |  4  |  9  |
| 'z' |  0  |  9  |
| 'z' |  0  |  8  |
| 'z' |  3  |  6  |
| 'z' |  2  |  7  |

在RoR中最有效的方法是什么?谢谢！

What is the most effecient way to achieve that in RoR? Thanks!

推荐答案

，您可以使用

you can use row_number, but you have to specify order or you will get unpredictable resutls

   with cte as (
       select
           *,
           row_number() over(partition by a order by b, c) as row_num
       from table1
   )
   select a, b, c
   from cte
   where row_num <= 5

这篇关于从每个不同的值中选择5个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！