SQlite 从表中为每个项目选择前 N 行

Question

我有一个如下所示的表，我试图按创建日期选择每个流的前 2 行。

    ID  Name    createdDate(string)
76  Stream1 2018-10-19T00:00:00
76  Stream1 2018-10-18T00:00:00 
76  Stream1 2018-10-17T00:00:00  
70  Stream2 2018-10-17T00:00:00 
70  Stream2 2018-10-03T00:00:00 
70  Stream2 2018-09-22T00:00:00 
21  Stream3 2018-09-21T00:00:00
21  Stream3 2018-09-20T00:00:00 
21  Stream3 2018-08-21T00:00:00 

输出如下所示，如何为此构建查询

76  Stream1 2018-10-19T00:00:00
76  Stream1 2018-10-18T00:00:00 
70  Stream2 2018-10-17T00:00:00 
70  Stream2 2018-10-03T00:00:00 
21  Stream3 2018-09-21T00:00:00
21  Stream3 2018-09-20T00:00:00 

Answer 1

从 SQLite 3.25 开始，您可以使用 ROW_NUMBER:

WITH cte AS (
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY Name
                      ORDER BY CAST(createdDate AS DATE) DESC) AS rn
  FROM tab
)
SELECT *
FROM cte
WHERE rn <= 2;