In MS SQL Server 2008 R2, I have a table Foo, and for every insert and update on Foo, I also insert into FooAuditLog the date, user, the PK FooId and a few of the other columns of Foo, including one numeric value, call it cxp.
I now need to retrieve the history of changes to cxp over time for a given FooId. But it is possible to save a Foo without changing cxp, and I need to ignore those values.
For example, if the Audit Log entries for a specific Foo (ie select date, user, cxp from FooAuditLog where fooId=17) look like this:
Date User Cxp
-------------------------
10/26 Fred 42
10/28 George 38
11/7 Tom 38
11/9 Fred 38
11/12 Joe 33
11/14 Tom 33
11/18 George 38
Then I need to modify the query to return only:
Date User Cxp
-----------------------------
10/26 Fred 42
10/28 George 38
11/12 Joe 33
11/18 George 38
And ignore the entries on 11/7, 11/9, and 11/14. I had considered a select distinct (cpx) ... group by date but I do need to capture the entry where the value changes back to a previous value.
You need to get the previous value. This version uses MySQL syntax with a correlated subquery to get the result:
select t.*
from (select t.*,
(select cxp from t t2 where t2.date < t.date order by date desc limit1
) as prevcxp
from t
) t
where prevcxp is NULL or prevcxp <> cxp
In other databases, you might use lag() instead of the subquery, the limit might be replaced by a top or even fetch first 1 row.