获取第n个连续组的第一行/最后一行

Question

从第n个组中选择单个记录/值的最简单方法是什么？该组由物料及其价格确定（价格可以更改）。我需要找到最后一个物料的最后一个日期和最后一个物料价格组的最后一个日期。所以我想知道什么时候确切的价格改变了。

我试过下面的查询以获取当前（最后）价格的第一个日期，如果以前使用过该价格，则该日期可能会返回错误的日期：

DECLARE @material VARCHAR(20)
SET @material = '1271-4303'

SELECT TOP 1 Claim_Submitted_Date 
FROM   tabdata
WHERE Material = @material 
AND Price = (SELECT TOP 1 Price FROM tabdata t2 
             WHERE Material = @material
             ORDER BY Claim_Submitted_Date DESC)
ORDER BY Claim_Submitted_Date ASC

这也只会返回最后一个，我该如何获取以前的？那么，上一个/第一个使用前一个价格的日期是什么？

我简化了我的架构，并使用sample-data创建了 这个sql-
fiddle
。按时间顺序排列。因此，我需要ID =
7的行，因为它具有最新日期的倒数第二个价格。

ID   CLAIM_SUBMITTED_DATE                   MATERIAL    PRICE
5   December, 04 2013 12:33:00+0000         1271-4303   20
4   December, 03 2013 12:33:00+0000         1271-4303   20   <-- current
3   November, 17 2013 10:13:00+0000         1271-4846   40
7   November, 08 2013 12:16:00+0000         1271-4303   18   <-- last(desired)
2   October, 17 2013 09:13:00+0000          1271-4303   18
1   September, 17 2013 08:13:00+0000        1271-4303   10
8   September, 16 2013 12:15:00+0000        1271-4303   17
6   June, 23 2013 14:22:00+0000             1271-4303   18
9   January, 11 2013 12:22:10+0000          1271-4303   20   <-- a problem since this is older than the desired but will be returned by my simply sub-query approach above

甚至可以对这个值进行参数化设置，因此nthLatestPriceGroup如果我想知道最后一个价格日期的第三位？请注意，查询位于标量值函数中。

编辑 ：非常感谢所有人。但不幸的ROW_NUMBER是，这里似乎没有一个简单的方法，因为我正尝试在给定材料 的当前价格之前 获得
最近价格 的行。因此，GROUP BY/PARTITION BY material,price包含价格相同但不属于最近的物料价格组的行。

考虑价格可以从

Date             Price     Comment
5 months ago     20        original price, note that this is the same as the curent which causes my query to fail!
3 months ago     18        price has changed, i might need the first and last date
2 months ago     20        price has changed, i might need the first and last date
1 month ago      18        previous price, i need the oldest and newest dates 
NOW              20        current price, i need the first/oldest date from this group

所以我想要最后20组的最新行的日期，最旧的20组是无关紧要的。因此，我必须以某种方式对 连续 价格进行分组，因为价格在更改后可以重复。

因此，实际上，我只需要上面列表Claim_Submitted_Date中以其开头的价格组中1 month ago ... previous price的最新价格，这是直到先前价格有效的日期。注释中列出的其他信息非常好（nthLatestPriceGroup子问题）。这是ID=7上面的示例数据中包含的行。顺便说一句，这个价格组中最老的行是ID=2（10月17日），而不是ID=6（6月23日），即使后者较旧。之后有一个不同的价格（10）。这就是为什么我不能使用简单的排名功能的原因。

Answer 1

您将需要在子查询中使用窗口函数ROWNUMBER，…

这样的事情将带您到那里：

ROW_NUMBER() OVER(PARTITION BY Price ORDER BY Claim_Submitted_Date DESC) AS Row

这是根据您的小提琴进行的更新：

DECLARE @material VARCHAR(20)
SET @material = '1271-4303'


SELECT * FROM
(
SELECT  *,
        ROW_NUMBER() OVER(PARTITION BY Material ORDER BY Claim_Submitted_Date ASC) AS rn  
FROM tabdata t2 
WHERE Material = @material
) res
WHERE rn=2

如果idData是增量的（因此按时间顺序），则可以使用以下方法：

SELECT * FROM
(
SELECT  *,
        ROW_NUMBER() OVER(PARTITION BY Material ORDER BY idData DESC) AS rn  
FROM tabdata t2 
WHERE Material = @material
) res

在查看您的最新要求时，我们可能会三思而后行（如果我理解正确的话）：

DECLARE @MATERIAL AS VARCHAR(9)
SET @MATERIAL = '1271-4303'

SELECT  TOP 1 *
FROM tabdata t2 
WHERE Material = @material
AND PRICE <> (  SELECT TOP 1 Price
                FROM tabdata 
                WHERE Material = @material 
                ORDER BY CLAIM_SUBMITTED_DATE desc)
ORDER BY CLAIM_SUBMITTED_DATE desc

--results
idData  Claim_Submitted_Date        Material    Price
7       2013-11-08 12:16:00.000     1271-4303   18

这是基于此的小提琴。