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

Question

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

So, in addition to the grouping columns and `timeStamp`, you also want *all other columns from the row with the maximum `timeStamp`*? What should happen in the event of ties for the maximum? — Damien_The_Unbeliever, Jan 22 '19 at 08:01
Possible duplicate of [sql server select first row from a group](https://stackoverflow.com/questions/7344731/sql-server-select-first-row-from-a-group)? — Thom A, Jan 22 '19 at 08:18

score 3 · Accepted Answer · answered Jan 22 '19 at 08:36

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

score 0 · Answer 2 · answered Jan 22 '19 at 08:24

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

I can see that you try to Group by BASICID. which has not any duplicate value or anything. If you want the result which you mentioned you need to remove that column and used it. But you still need that.. use this query

select * from [test].[dbo].[alertdetails] as a
inner join 
(
select  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]
) as b
on a.timeStamp=b.t and a.alertType=b.alertType and a.alertTypeId=b.alertTypeId

hope this work. PS. I did not run the query so there might some changes you may require to do.

score 0 · Answer 3 · answered Jan 22 '19 at 08:32

Windowing function such as Row Number can be used here to fetch the row you are interested in from a partitioned group.

In this case, you can partition your window using the the alertType and and alertTypeId, order the sorted window by timeStamp (DESC in this case). The Row Number will create a new physical column in your dataset with values such as 1,2,3... basis the number of items it finds in the partitioning bucket.

All that remains now is to fetch all row numbers where the value is 1.

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

3 Answers3