我正在使用以下代码: (来自这个问题: 如何在SQL中获取每个组的最后一条记录,将其替换为我的列)
WITH e AS
(
SELECT *,
ROW_NUMBER() OVER
(
PARTITION BY ApplicationId
ORDER BY theDate DESC
) AS Recency
FROM [Event]
)
SELECT *
FROM e
WHERE Recency = 1
如果两个字段相同,是否可以进行“分区”?例如,我有以下数据:
ID Name theDate
123 John 01/01/2012
123 John 01/02/2012
123 Doe 01/01/2012
456 Smith 02/04/2012
789 Smith 02/01/2012
789 Smith 02/09/2012
789 Roger 02/08/2012
从那些数据中,我想要返回:
ID Name theDate
123 John 01/02/2012
123 Doe 01/01/2012
456 Smith 02/04/2012
789 Smith 02/09/2012
789 Roger 02/08/2012
感谢任何帮助。
Thomas
MAX(theDate)
和GROUP BY ID, Name
来完成。 - Sean