Showing only those rows where data changed.

September 1, 2011 at 4:43 pm | Posted in Oracle Developement | Leave a comment

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
(SELECT dt
,val
,lag(val, 1, 0) over(ORDER BY dt) pre_val
FROM t1)
SELECT dt,val
FROM myqry
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
Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Entries and comments feeds.

%d bloggers like this: