获取每个不同费率名称的最新费率 [英] Get Latest Rates For Each Distinct Rate Name

问题描述

RateName     Rate    EffectiveDate
--------     ----    -------------
RateOne      400     2011-01-05
RateOne      410     2011-06-31
RateTwo      147     2010-09-21
RateThree    68      2011-07-14
RateTwo      100     2011-10-30

如果我有上述数据，如何运行查询以获取这些数据结果：

If I have data such as the above, how can I run a query such that I'll have these as results:

RateName     Rate    EffectiveDate
--------     ----    -------------
RateOne      410     2011-06-31
RateThree    68      2011-07-14
RateTwo      100     2011-10-30       

基本上，我只需要每个不同费率名称的最新费率。

Basically, I just need the latest rates for each distinct rate name.

推荐答案

您可以尝试以下操作：

SELECT A.*
FROM YourTable A
INNER JOIN (    SELECT RateName, MAX(EffectiveDate) AS MaxDate
                FROM YourTable
                GROUP BY RateName) B
ON A.RateName = B.RateName AND A.EffectiveDate = B.MaxDate

这篇关于获取每个不同费率名称的最新费率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！