Let’s say you have a table where the values of a particular column only changes slowly over time, perhaps a parameter of some sort. Now you want to write a query that shows only those rows of the table of when the value in that column changed.
Take the following table for example: It has 21 rows. The first column (DT) shows a date, the second column (VAL) shows a value that slowly changes over time:
DT VAL 01-Sep-11 10 02-Sep-11 10 03-Sep-11 10 04-Sep-11 10 05-Sep-11 20 06-Sep-11 20 07-Sep-11 20 08-Sep-11 20 09-Sep-11 10 10-Sep-11 10 11-Sep-11 10 12-Sep-11 30 13-Sep-11 30 14-Sep-11 30 15-Sep-11 30 16-Sep-11 30 17-Sep-11 30 18-Sep-11 10 19-Sep-11 10 20-Sep-11 10 21-Sep-11 10
Now write a query that only returns the rows for those dates where the value changes, i.e. 9/1 (the first row), 9/5, 9/9, 9/12, and 9/18.
To do this we could try to use the LAG function in the WHERE clause and only return those rows where VAL is different from VAL of the previous row. But since we cannot use a windowing function in a WHERE clause, we’ll have to put it into a factored subquery first:
WITH myqry AS
,lag(val, 1, 0) over(ORDER BY dt) pre_val
WHERE val != pre_val;
DT VAL 01-Sep-11 10 05-Sep-11 20 09-Sep-11 10 12-Sep-11 30 18-Sep-11 10