获取最后一个副本
问题描述:
我的问题是,因为人们对我们的运输订单犯了错误,并且创建了另一个追踪#来修正订单,因此存在欺诈行为。所以我总是需要得到订单的最后一个副本,所以我得到正确的跟踪#。获取最后一个副本
我已经试过这样:
SELECT OrderNum, TrackingNumber, CreatedDate, CreatedBy, Carrier
FROM FPDWDB1.Ship.dbo.ShippingLog
GROUP BY OrderNum
我收到以下错误:
Column 'FPDWDB1.Ship.dbo.ShippingLog.TrackingNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
答
Select OrderNum, TrackingNumber, CreatedDate, CreatedBy, Carrier
FROM (
SELECT OrderNum, TrackingNumber, CreatedDate, CreatedBy, Carrier
,ROW_NUMBER() OVER (PARTITION BY OrderNum ORDER BY CreatedDate DESC) rn
FROM FPDWDB1.Ship.dbo.ShippingLog
) A Where rn = 1
OR
WITH A AS (
SELECT OrderNum, TrackingNumber, CreatedDate, CreatedBy, Carrier
,ROW_NUMBER() OVER (PARTITION BY OrderNum ORDER BY CreatedDate DESC) rn
FROM FPDWDB1.Ship.dbo.ShippingLog
)
Select OrderNum, TrackingNumber, CreatedDate, CreatedBy, Carrier
FROM A
Where rn = 1
如果使用GROUP BY,所有其他列以外该组需要被用在一个聚合函数中,即max,avg等等。它听起来像像你需要一个row_number(),它是决定最新记录的CreatedDate? – artm