仅选择多行的最大值记录

Question

我有一个表，其中包含如下记录

Unit    Rate    Date-effected
-------------------------------------------
ALHA    ILS     2014-03-02 00:00:00.000
ALHA    ILS     2014-08-02 00:00:00.000
BUCK    ILS     2013-02-14 00:00:00.000
BUCK    ILS     2014-03-02 00:00:00.000
BUCK    ILS     2014-08-02 00:00:00.000
CASC    ILD     2013-02-14 00:00:00.000
CASC    ILD     2014-03-02 00:00:00.000
CASC    ILD     2014-08-02 00:00:00.000   

现在，我只想在result table中选择最大日期值记录。那就是，

Unit    Rate    DateEffected
-------------------------------------------
ALHA    ILS     2014-08-02 00:00:00.000
BUCK    ILS     2014-08-02 00:00:00.000
CASC    ILD     2014-08-02 00:00:00.000   

Answer 1

希望这能有所帮助。

SELECT
 Unit,
 Rate,
MAX(DateEffected) AS MaxDateEffected
FROM TableName
GROUP BY  Unit,Rate

Answer 2

您可以使用来执行以下操作：

SELECT Unit, Rate, DateEffected
FROM (
  SELECT Unit, Rate, DateEffected,
         ROW_NUMBER() OVER (PARTITION BY Unit 
                            ORDER BY DateEffected DESC) AS rn
FROM mytable) AS t
WHERE t.rn = 1