Please could someone help me in finding a solution for the below example?
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.
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 | +-----+---------------------+----------+------+--------+------------+-------------+-------------------+ 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 ) 
Id sequential as it is in your example?