选择“GroupBy”中的所有值

Question

我有一个场景，其中我的表中有三列。ID(String)，Desc (String) TerminationDate, Last Update Date(Time)。没有主键，所以可能有多行具有相同的数据集，但LastUpdate Date总是不同的。我需要写一个SP，其中我需要获得最新修改的结果(ID,Desc，终止日期)。请看下面的例子

ID    Desc    TerminationDate    LastUpdtDt
A     test     01-01-2013        01-01-2013
A     test1     01-03-2013        25-01-2013
A     test     01-01-2013        26-03-2013
B     test     01-01-2011        01-01-2013
The result i shuld get is 
A     test     01-01-2013        26-03-2013
B     test     01-01-2011        01-01-2013

如果您需要更多的信息，请告诉我。

Answer 1

SELECT  ID, [DESC], TerminationDate, LastUpdtDt
FROM
        (
            SELECT  ID, [DESC], TerminationDate, LastUpdtDt,
                    ROW_NUMBER() OVER(PARTITION BY ID 
                    ORDER BY LastUpdtDt DESC) rn
            FROM    TableName
        ) ss
WHERE   rn = 1

<
• SQLFiddle演示/gh>

Answer 2

您可以使用子查询返回具有max(LastUpdtDt)的行:

select t1.id,
  t1.[desc],
  t1.terminationdate,
  t1.LastUpdtDt
from yt t1
inner join
(
  select max(LastUpdtDt) LastUpdtDt, id
  from yt
  group by id
) t2
  on t1.id = t2.id
  and t1.LastUpdtDt = t2.LastUpdtDt;

Answer 3

SELECT ID，DESC,TERMINATION_DATE,LAST_UPDATE从(select id，DESC,TERMINATION_DATE,LAST_UPDATE,Row_number () over (partition by id order by last_update desc) serial_order从LAST_UPDATEB)其中serial_order = 1