SQL按最近日期选择行

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

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

所以这不是请求者所要求的,而是"SQL按最近日期选择行"的答案.

修改自http://wiki.lessthandot.com/index.php/Returning_The_Maximum_Value_For_A_Row

SELECT t.chargeId, t.chargeType, t.serviceMonth FROM( 
    SELECT chargeId,MAX(serviceMonth) AS serviceMonth
    FROM invoice
    GROUP BY chargeId) x 
    JOIN invoice t ON x.chargeId =t.chargeId
    AND x.serviceMonth = t.serviceMonth

SELECT chargeId, chargeType, MAX(serviceMonth) AS serviceMonth 
FROM invoice
GROUP BY chargeId, chargeType