SQL按最近日期选择行 [英] SQL selecting rows by most recent date

问题描述

使用以下查询和结果，我正在寻找ChargeId和ChargeType唯一的最新条目.

Using the following query and results, I'm looking for the most recent entry where the ChargeId and ChargeType are unique.

select chargeId, chargeType, serviceMonth from invoice

    CHARGEID    CHARGETYPE  SERVICEMONTH
1   101     R       8/1/2008
2   161     N       2/1/2008
3   101     R       2/1/2008
4   101     R       3/1/2008
5   101     R       4/1/2008
6   101     R       5/1/2008
7   101     R       6/1/2008
8   101     R       7/1/2008

所需:

    CHARGEID    CHARGETYPE  SERVICEMONTH
1   101     R       8/1/2008
2   161     N       2/1/2008

推荐答案

您可以使用 GROUP BY 对类型和ID进行分组.然后，您可以使用 MAX()汇总函数来获取最近的服务月份.下面返回带有ChargeId，ChargeType和MostRecentServiceMonth的结果集

You can use a GROUP BY to group items by type and id. Then you can use the MAX() Aggregate function to get the most recent service month. The below returns a result set with ChargeId, ChargeType, and MostRecentServiceMonth

SELECT
  CHARGEID,
  CHARGETYPE,
  MAX(SERVICEMONTH) AS "MostRecentServiceMonth"
FROM INVOICE
GROUP BY CHARGEID, CHARGETYPE

这篇关于SQL按最近日期选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！