sql - 在sqlserver中如何从每个组中获取最后一个值（时间）

Question

在 sql server 我有下面的表结构

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

我正在尝试将具有最大时间戳的 alertType、alertTypeId、macid 分组（如果时间戳也相同，则每组应该只返回一个数据）。

我正在使用以下查询

SELECT  a.basicid, 
        a.[alertType], 
        a.[alertTypeId], 
        a.[macId],  
        MAX(a.timeStamp) as t  
FROM [test].[dbo].[alertdetails] as a 
GROUP BY a.[alertType], a.[alertTypeId], a.[macId], a.basicid 
ORDER BY a.basicid

但是正在返回所有数据。

我想要的最终数据是

basicid alertType   alertTypeId alertDescription    macId     timeStamp     companyId     alertName           alertCondition    unitType      channelType      alertValue    expectedValue
1234    406          123         test               13446   1547722123000    1234         test data              test          Centimeters       length           50              60
1295    409          127         test               13448   1547722123000    1234         test data              test          Centimeters       length           50.2            60.3
1237    408          123         test               13446   1547722123000    1234         test data              test          Centimeters       length           50.2            60.3
1256    409          128         test               13448   1548135899000    1234         test data              test          Centimeters       length           50.2            60.3

标签： sqlsql-server

Answer 1

您可以使用ROW_NUMBER()以下查询轻松完成此操作。

SELECT * 
FROM   (SELECT *, 
               Row_number() 
                 OVER( 
                   partition BY alerttype, alerttypeid, macid 
                   ORDER BY timestamp DESC) RN 
        FROM   [test].[dbo].[alertdetails]) T 
WHERE  rn = 1