Q:

In sqlserver how to get the lastvalue(time) from each group

In sql server I am having the below table structure
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

I am trying group the alertType,alertTypeId,macid having maximimum timestamp(should return only one data per group if timestamp is same also). 
I am using the below query 
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

But is returning all data.
The final data I wanted is
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

A:

You can do it using ROW_NUMBER() easily like following query.
SELECT * 
FROM   (SELECT *, 
               Row_number() 
                 OVER( 
                   partition BY alerttype, alerttypeid, macid 
                   ORDER BY timestamp DESC) RN 
        FROM   [test].[dbo].[alertdetails]) T 
WHERE  rn = 1 

