CODEWITHSUNDEEP
sql-serversql-server-2008
satya
satya

Reputation: 373

SQL Server - Find Duplicate entries in a column

Please could someone help me in finding a solution for the below example?

enter image description here

every work carried out by a user (OperatorID) should have IN & OUT, if for an instance the user enter either IN twice or OUT twice in a consecutive rows then I need to take the latest transaction ONLY onto my results.

So, in my above example ID (102) is what we are interesting in and ignoring the rest.

"Location" can be any thing i.e. SI/SO, D1/DO and so on.

Upvotes: 0

Views: 54

Answers (1)

SqlZim
SqlZim

Reputation: 38023

using exists() to get the second row of the duplicate where id=id-1 and operatorid and location are the same:

select * 
from t
where exists (
  select 1
  from t i
  where i.OperatorId = t.OperatorId 
    and i.id = t.id-1
    and i.Location = t.Location
)

returns:

+-----+---------------------+----------+------+--------+------------+-------------+-------------------+
| id  |      datetime       | location | site | client | operatorId | containerId |    WorkflowId     |
+-----+---------------------+----------+------+--------+------------+-------------+-------------------+
| 102 | 2017-02-03 11:10:21 | PO       | AA   | TEMP   | Test1      |         451 | 123F-258G-369147C |
+-----+---------------------+----------+------+--------+------------+-------------+-------------------+

If Id is not sequential, you could use a common table expression with row_number():

with t as (
  select *
    , rn = row_number() over (order by id)
  from #temp
)

select * 
from t
where exists (
  select 1
  from t i
  where i.OperatorId = t.OperatorId 
    and i.rn = t.rn-1
    and i.Location = t.Location
)

Upvotes: 2

Related Questions