这样的事情将带您到那里:
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
这是基于此的小提琴。