检索每个客户的最新记录 [英] retrieve the most recent record for each customer

问题描述

我有以下数据:

ID   NAME   DATE
3    JOHN   2011-08-08
2    YOKO   2010-07-07
1    JOHN   2009-06-06

代码(对于SQL Server 2005):

Code (for SQL Server 2005):

DECLARE @TESTABLE TABLE (id int, name char(4), date smalldatetime) 
INSERT INTO @TESTABLE VALUES (3, 'JOHN', '2011-08-08')
INSERT INTO @TESTABLE VALUES (2, 'YOKO', '2010-07-07')
INSERT INTO @TESTABLE VALUES (1, 'JOHN', '2009-06-06')

我想为每个名称获取具有最新DATE的ID.像这样:

I want to get, for each NAME, the ID that has the most recent DATE. Like this:

3    JOHN   2011-08-08
2    YOKO   2010-07-07

实现这一目标的最优雅的方法是什么?

What is the most elegant way of accomplishing this?

推荐答案

;WITH x AS 
(
    SELECT ID, NAME, [DATE], 
      rn = ROW_NUMBER() OVER 
      (PARTITION BY NAME ORDER BY [DATE] DESC)
    FROM @TESTABLE
)
SELECT ID, NAME, [DATE] FROM x WHERE rn = 1
  ORDER BY [DATE] DESC;

尝试避免使用诸如[DATE] ...

Try to avoid reserved words (and vague column names) like [DATE]...

这篇关于检索每个客户的最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！