CODEWITHSUNDEEP
sqlsql-servert-sql
Josh
Josh

Reputation: 587

T-SQL query to show only differences between rows

I have a query that outputs the following dataset:

enter image description here

I need to find a way to find instance where the unitid column and the stat column do not match. An example would be if unitid = 2, CleanCode = 10 and stat = Started and the next row below was unitid = 2, CleanCode = 2 and stat = Not Started.

How can I create a query to find those differences?

Thanks

Upvotes: 0

Views: 196

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

One option is a self join:

SELECT DISTINCT t1.unitId
FROM yourTable t1
INNER JOIN yourTable t2
    ON t1.unitId = t2.unitId AND
       t1.id <> t2.id AND
       t1.cleanCode <> t2.cleanCode AND
       t1.stat <> t2.stat;

Demo

I added one extra record to your sample data, because none of the data you actually showed us would end up in your expected result set.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Assuming you have a column that specifies the ordering, use lag():

select t.*
from (select t.*,
             lag(stat) over (partition by unitcode order by <ordering column>) as prev_stat
      from t
     ) t
where prev_stat <> stat;

Upvotes: 0

Related Questions