Get Max Id Row From Individual Rows In Sql Server

I Have data like below with multiple rows with same data which can be identified by ID. I Need the data like below. Get only individual max ID value for every set of duplicate rec

Solution 1:

You can filter with a subquery. Assuming that your table's columns are called id, date and col, that would be:

select t.*
from mytable t
where t.col = (selectmax(t1.col) from mytable t1 where t1.id = t.id)

For performance, consider an index on (id, col).

Solution 2:

An efficient method -- with the right index -- is a correlated subquery:

select t.*
from t
where t.individual = (selectmax(t2.individual) from t t2 where t2.id = t.id);

The right index is on (id, individual).

Solution 3:

This should help you

createtable #sample (type char(1), date datetime, Id bigint)
insertinto #sample values('A', '5/22/2019 4:33', 1065621)
insertinto #sample values('A', '5/22/2019 4:33', 1065181)
insertinto #sample values('A', '5/22/2019 4:33', 1064212)
insertinto #sample values('B', '11/7/2017 1:07', 540180)
insertinto #sample values('B', '11/7/2017 1:07', 540179)
insertinto #sample values('B', '11/7/2017 1:07', 540177)

select*from #sample

select [type], [date], max(id)
from #sample
groupby [type], [date]

selectdistinct [type], [date], max(id) over(partitionby  [type], [date] )
from #sample

Droptable #sample