SQL or LINQ: how do I select records where only one paramater changes?

Question

Say we have this list:

Id  IdRef   myColumn    anotherColumn
448    70      1            228
449    70      1            2s8
451    70      1            228
455    70      2            2a8
456    70      2            s28
457    70      2            28
458    70      3            v
459    70      3            28
460    70      4            22
461    70      3            54
462    70      4            45
463    70      3            s28

I need to select a list with a record everytime "myColumn" changes. So the result would be:

    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

Along with the problem you should **include the code you have up until this point** (*make some effort to answer your problem/question as [so] is not a code writing service*). After [doing more research](http://meta.stackoverflow.com/questions/261592) if you have a problem you can **post what you've tried** with a **clear explanation of what isn't working** and providing a **[mcve]**. I suggest reading [*How do I ask a Good Question*](/help/how-to-ask) and [*Writing the Perfect Question*](http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/). Also, be sure to take the [tour]. — Igor, Feb 04 '20 at 14:25

score 3 · Accepted Answer · answered Feb 04 '20 at 14:34

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(partition by IdRef order by Id) lagMyColumn
    from mytable t
) t
where lagMyColumn is null or 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

SQL or LINQ: how do I select records where only one paramater changes?

1 Answers1

Linked

Related