SQL 按最近的日期选择行，并带有两个唯一的列 [英] SQL selecting rows by most recent date with two unique columns

问题描述

使用以下查询和结果，我正在寻找 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屋！