CODEWITHSUNDEEP
sqlsql-serversql-server-2016
Hosein Masoomi
Hosein Masoomi

Reputation: 197

aggregate value in different rows

i have data like this table 

ItemId  Value   Date
1        2      2017-12-18 17:00:00.000
1        2      2017-12-18 17:02:00.000
1        2      2017-12-18 17:04:00.000
1        3      2017-12-18 17:06:00.000
1        3      2017-12-18 17:08:00.000
1        2      2017-12-20 17:10:00.000
1        2      2017-12-20 17:12:00.000

i want to output like this in sql server

ItemId     Value   MaxDate

1          2       2017-12-18 17:04:00.000
1          3       2017-12-18 17:08:00.000
1          2       2017-12-20 17:12:00.000

I'm sorry, I did not get a better sentence for my title

Upvotes: 0

Views: 92

Answers (3)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

This can be done with a difference of row numbers approach. Look at the results of the inner query that classifies consectuive rows with the same value into one group which can be used to get the final result.

select itemid,value,max(date)
from (
select t.*,
row_number() over(partition by itemid order by date)
-row_number() over(partition by itemid,value order by date) as grp
from tbl t
) t
group by itemid,value,grp

Edit: Just getting the min or max would be easier with Gordon's answer. However, if both min, max and sum and other aggregations are needed per group, this version would be better.

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

Not sure if you are trying to do a basic aggregation where you want the max datetime value for each day or you are trying to do an islands and gaps problem where the group is defined because the "previous" row changes value. But I think you just need basic aggregation.

Here is a fully working example which return the output you stated you want.

declare @Something table
(
    ItemId int
    , Value int
    , [Date] datetime
)
insert @Something values
(1, 2, '2017-12-18 17:00:00.000')
, (1, 2, '2017-12-18 17:02:00.000')
, (1, 2, '2017-12-18 17:04:00.000')
, (1, 3, '2017-12-18 17:06:00.000')
, (1, 3, '2017-12-18 17:08:00.000')
, (1, 2, '2017-12-20 17:10:00.000')
, (1, 2, '2017-12-20 17:12:00.000')

select ItemId
    , Value
    , MaxDate = Max([Date])
from @Something
group by ItemId
    , Value
    , convert(Date, [Date])
order by MAX([Date])

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You want the rows where the next row has a different value:

select t.*
from (select t.*,
             lead(value) over (partition by itemid order by date) as next_value
      from t
     ) t
where next_value is null or next_value <> value;

Upvotes: 1

Related Questions