CODEWITHSUNDEEP
sql-servert-sql
JD136
JD136

Reputation: 175

T-SQL Getting Last Value for a Given Group

I am trying to get the last value in a group of values (a partition), but am having a little trouble. In the example below, you can see an example data set I'm working with. MAX() won't work as the "last" one here is the one with the last date in the partition (ID). I have LAST_VALUE commented out in the query below, but that seemingly doesn't work either... not sure what I'm doing wrong. What I'd like is a result data-set like this:

result data-set

create table #Example (ID int, [Date] datetime, Rating tinyint)

insert into #Example (ID, [Date], Rating)
values
    (5227,  '2021-01-11 19:21:39.107',  1)
    ,(128,  '2020-06-16 17:32:15.653',  5)
    ,(128,  '2020-06-23 11:35:16.570',  1)
    ,(2038, '2020-09-14 13:13:48.253',  2)
    ,(1268, '2020-07-30 16:05:07.950',  0)
    ,(2398, '2020-09-30 13:51:21.813',  5)
    ,(2058, '2020-09-14 13:13:48.253',  1)
    ,(3396, '2020-11-05 12:04:16.043',  5)
    ,(3396, '2020-12-07 13:23:05.357',  3)
    ,(3148, '2020-11-05 12:04:16.043',  1)
    ,(3399, '2020-11-06 12:04:16.043',  2)
    ,(3399, '2020-12-08 13:23:05.357',  4)

select
    exm.ID
    , exm.Date
    , exm.Rating
    --, LAST_VALUE(exm.Rating) over (partition by exm.ID order by exm.Date) as [FinalRating]
from 
    #Example as exm

drop table #Example

I'd be greatly appreciative of any assistance!

Upvotes: 2

Views: 61

Answers (1)

BJones
BJones

Reputation: 2460

Perhaps a slight modification to your query will work:

create table #Example (ID int, [Date] datetime, Rating tinyint)

insert into #Example (ID, [Date], Rating)
values
    (5227,  '2021-01-11 19:21:39.107',  1)
    ,(128,  '2020-06-16 17:32:15.653',  5)
    ,(128,  '2020-06-23 11:35:16.570',  1)
    ,(2038, '2020-09-14 13:13:48.253',  2)
    ,(1268, '2020-07-30 16:05:07.950',  0)
    ,(2398, '2020-09-30 13:51:21.813',  5)
    ,(2058, '2020-09-14 13:13:48.253',  1)
    ,(3396, '2020-11-05 12:04:16.043',  5)
    ,(3396, '2020-12-07 13:23:05.357',  3)
    ,(3148, '2020-11-05 12:04:16.043',  1)
    ,(3399, '2020-11-06 12:04:16.043',  2)
    ,(3399, '2020-12-08 13:23:05.357',  4)

select
    exm.ID
    , exm.Date
    , exm.Rating
    , last_value(exm.Rating) over (partition by exm.id order by exm.date
                                          rows between current row and unbounded following) as [FinalRating]
from 
    #Example as exm
order by id, date

Upvotes: 3

Related Questions