sql - 如何获取每组具有最大时间戳的每条记录

Question

在 SQL Server 中，我有下面的表结构

alert_details_status表：

alertTypeId macId     timeStamp     is_notified  escalation_status  alertType
123         13446   1547722123000       true       completed          408

alert_details表：

basicid alertType   alertTypeId alertDescription    macId     timeStamp   companyId     alertName            alertCondition  unitType   channelType  alertValue expectedValue
1234     406            123         testalert       13446   1547722123000   1234     test alert name            testalert   Centimeters   length        50          60
1295     409            127         testalert       13448   1547722123000   1234     test alert name            testalert   Centimeters   length        50.2        60.3
1298     409            128         testalert       13448   1547722123000   1234     test alert name            testalert   Centimeters   length        50.2        60.3
1238     408            123         testalert       13446   1547722123000   1234     test alert name            testalert   Centimeters   length        50.2        60.3
1255     409            128         testalert       13448   1548135899000   1234     test alert name            testalert   Centimeters   length        50.2        60.3
1256     409            128         testalert       13448   1548135899000   1234     test alert name            testalert   Centimeters   length        50.2        60.3
1251     408            123         testalert       13446   1548148705000   1234     test alert name            testalert   Centimeters   length        50.2        60.3

如果alert_details_status表记录timestamp >30 minutes(compared to present time)对于每组alertTypeId, macId,alertType具有is_notifiedtrue 并且escalation_status已完成，则应选择对应alertTypeId的 , macId,具有每个记录的最大时间戳（每组最多一条记录，即使组的时间戳相同）。alertType

如何得到上述结果。我尝试使用以下查询，但它什么也没返回。实际上它应该返回 1251 基本 id 记录，因为它在alert_details_status table having timestamp >30 min compared to present timestamp. So for that alertTypeId ,macId alertType it should return maximum of timestamp for a group having alertTypeId ,macId alertType(i,e 1251).

SELECT * 
FROM   (SELECT t1.*, 
               Row_number() 
                 OVER( 
                   partition BY t1.alerttype, t1.alerttypeid, t1.macid 
                   ORDER BY t1.timestamp DESC) rn 
        FROM   [test].[dbo].[alertdetails] as t1
               RIGHT JOIN [test].[dbo].[alert_details_status] t2 
                      ON t1.macid = t2.macid 
                         AND t1.alerttypeid = t2.alerttypeid 
                         AND t1.alerttype = t2.alerttype 
                         AND t2.is_notified = 'true '
                         AND t2.escalation_status = 'completed' 
        WHERE  ((DATEDIFF(s, '1970-01-01', GETUTCDATE())-(t2.timeStamp/1000))/60)>4400 and t2.alerttypeid IS NULL  ) t 
WHERE  t.rn = 1

我想要的结果是：

  basicid   alertType   alertTypeId alertDescription    macId     timeStamp   companyId     alertName            alertCondition  unitType   channelType  alertValue expectedValue
    1251     408            123         testalert       13446   1548148705000   1234     test alert name            testalert   Centimeters   length        50.2        60.3

标签： sqlsql-server