CODEWITHSUNDEEP
sqlsql-server
H22
H22

Reputation: 169

How can I find the minimum value from duplicate row numbers?

I need to find the minimum date value for 'LastUpdatedAt' but only between the same rowId's

I made a temporary table '#results' to include the 'rowId' and 'LastUpdatedAt' fields. I need to find the minimum date for the RowId. For example if there are two entries with the rowId '9' I must find the LastUpdatedAt that occurred first. I tried using the MIN(LastUpdatedAt) but that only return the minimum date for the entire table.

create table #results(
    RowId       id_t,
    LastUpdatedAt date_T
)


insert into #results
select H.RowId, H.LastUpdatedAt from MemberCarrierMap M Join MemberCarrierMapHistory H on M.RowId = H.RowId

select * from #results


RowId   LastUpdatedAt

9       2010-01-21 17:07:48.640
9       2018-05-14 13:33:04.313
88      2016-11-22 14:49:13.770


update MemberCarrierMap 
set CreatedAt = (select MIN(LastUpdatedAt) from #results)
Where CreatedAt is null

I tried this but it only finds the minimum for the entire table. Is there anyway to just find the minimum date for one rowID?

Upvotes: 1

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You want a correlation clause:

update MemberCarrierMap 
set CreatedAt = (select MIN(LastUpdatedAt) from #results r where r.rowId = MemberCarrierMap.rowId)
Where CreatedAt is null;

Upvotes: 1

Related Questions