Sql Or Linq: How Do I Select Records Where Only One Paramater Changes?

Say we have this list: Id IdRef myColumn anotherColumn 448 70 1 228 449 70 1 2s8 451 70 1 228 455 70 2

Solution 1:

This is a gaps and islands problem. In SQL, here is one approach to solve it using window functions:

select Id, IdRef, myColumn, anotherColumn
from (
    select t.*, lag(myColumn) over(partitionby IdRef orderby Id) lagMyColumn
    from mytable t
) t
where lagMyColumn isnullor lagMyColumn <> myColumn

The inner query recovers the value of myColumn on the previous row, ordered by Id. Then the outer query filters on records where that value is different from the one on the current row.

Demo on DB Fiddle:

 id | idref | mycolumn | anothercolumn
--: | ----: | -------: | :------------
448 |    70 |        1 | 228          
455 |    70 |        2 | 2a8          
458 |    70 |        3 | v            
460 |    70 |        4 | 22           
461 |    70 |        3 | 54           
462 |    70 |        4 | 45           
463 |    70 |        3 | s28