CODEWITHSUNDEEP
sqlsql-serverwhere-clausegreatest-n-per-group
Vinay
Vinay

Reputation: 17

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.

enter image description here

I Need the data like below. Get only individual max ID value for every set of duplicate records with can be done by taking individual max ID

enter image description here

Can you help me on this?

Upvotes: 1

Views: 63

Answers (3)

Muhammed Nigil
Muhammed Nigil

Reputation: 183

This should help you


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

select * from #sample

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

select distinct [type], [date], max(id) over(partition by  [type], [date] )
from #sample

Drop table #sample

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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

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

The right index is on (id, individual).

Upvotes: 1

GMB
GMB

Reputation: 222432

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 = (select max(t1.col) from mytable t1 where t1.id = t.id)

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

Upvotes: 1

Related Questions