Sql: Retrieve Only the Records Whose Value Has Changed

return row where column value changed from last change

use lag() to find the last change (order by RecordAddedDate desc) in PartNumberID.

cumulative sum sum(isChange) to group the related rows under same group no. grp = 0 with be the rows of the last change

To get the min - RecordAddedDate, use row_number()

with 
cte1 as
(
    select *,
           isChange = case when PartNumberID
                           =    isnull(lag(PartNumberID) over (partition by Location 
                                                                   order by RecordAddedDate desc), 
                                       PartNumberID)
                           then 0
                           else 1
                           end
    from   mytable
),
cte2 as
(
    select *, grp = sum(isChange) over (partition by Location order by RecordAddedDate desc)
    from   cte1
),
cte3 as
(
    select *, rn = row_number() over (partition by Location order by RecordAddedDate)
    from   cte2 t
    where  t.grp = 0
)
select *
from   cte3 t
where  t.rn = 1

db<>fiddle demo

SQL Return last record where column value changed and amount of change

First with a CTE which returns all the changes and then with NOT EXISTS that returns the last change for each Entity:

(Edited with the actual table and column names)

with cte as (
  select t.hUnit UnitID, (t.cRent - tt.cRent) cRentChange, t.dtDate DateofChange
  from unit_history t inner join unit_history tt
  on 
    tt.hUnit = t.hUnit 
    and 
    tt.dtDate = (
      select max(dtDate) 
      from unit_history 
      where hUnit = t.hUnit and dtDate < t.dtDate
    )
  where t.cRent <> tt.cRent
)

select c.* from cte c
where not exists (
  select 1 from cte
  where UnitID = c.UnitID and DateofChange > c.DateofChange
)

See the demo.

Results:

 Entity | ValueChange | DateofChange       
> :----- | ----------: | :------------------
> A      |         100 | 01/01/2018 00:00:00
> B      |         150 | 01/01/2019 00:00:00

Selecting rows where a value has changed

select p1.date_entered, 
       p1.WholeSale,
       p1.Volume,
       p1.Clearance 
FROM pricetable p1
CROSS APPLY
--cross apply to most recent prior record
(SELECT TOP 1 * 
        FROM pricetable p2 
            where p1.product = p2.product 
                and p2.date_entered < p1.date_entered 
                    order by p2.date_entered desc) CA
where p1.product = 'TANGO'
  and (p1.wholesale != CA.wholesale or p1.volume != CA.volume or p1.clearence != CA.clearence)
order by p1.date_entered desc

SQL How to retrieve first row when value changed?

I understand that you want the first 1 - for series that start with 0 only.

If you are running MySQL 8.0, you can use window functions for this:

select id, min_date
from (
    select 
        id,
        flag,
        row_number() over(partition by id order by date) rn,
        min(case when flag = 1 then date end) over(partition by id) min_date
    from mytable
) t
where rn = 1 and flag = 0

The subquery ranks records having the same id by date and computes the date of the first 1 flag in the group; thie outer query filters on the first record per group, ensures that it has a 0 flag, and displays the computed date.

Select rows where value changed in column

In SQL Server 2012+, you would use lead():

select t.*
from (select t.*,
             lead(charge_code) over (partition by account order by postingdate) as next_charge_code
      from t
     ) t
where charge_code <> next_charge_code;

In earlier versions of SQL Server, you can do something similar with apply.

Related Topics