CODEWITHSUNDEEP
sql-servert-sqldistinct
MicrosoctCprog
MicrosoctCprog

Reputation: 490

Select while ignore duplicate by 2 rows with SQL Server

I have this table scan:

id   ip           port  text
-----------------------------
1    192.168.0.1    2   text1
2    192.168.0.1    2   text2
3    192.168.0.1    2   text3
4    192.168.0.3    2   text4

I want to select but ignore duplicate by ip+port by last id , so the results will be

4, 192.168.0.3,2, text4
3, 192.168.0.1,2, text3

How can I do that with a T-SQL query?

Upvotes: 0

Views: 26

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270703

You can use row_number():

select t.*
from (select t.*,
             row_number() over (partition by ip, port order by id desc) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 2

Related Questions