Filter consecutive records that have same value as preceding record

Database

Filter consecutive records that have same value as preceding record

I need help implementing a query that filters all consecutive records having same values for either COL_2 or COL_3 and  return only those that had a change in COL_2 or COL_3 against the previous record.

Sample Data:

COL_1 (DATETIME) | COL_2 (CHAR)| COL_3 (INT)

12/11/2010 2:58:33 PM | F | 1

12/11/2010 2:59:29 PM | F | 1

12/11/2010 3:08:27 PM | T | 1

12/11/2010 3:28:13 PM | F | 1

12/11/2010 2:58:33 PM | F | 1

12/11/2010 2:59:29 PM | F | 1

12/11/2010 3:08:27 PM | T | 1

12/11/2010 3:28:13 PM | T | 0

12/11/2010 3:38:44 PM | T | 0

Desired Output:

12/11/2010 2:58:33 PM | F | 1

12/11/2010 3:08:27 PM | T | 1

12/11/2010 2:59:29 PM | F | 1

12/11/2010 3:08:27 PM | T | 1

12/11/2010 3:28:13 PM | T | 0

In summary, I need to discard all records where the values for COL_2 & COL_3 did not change when compared to previous row.

Thanks

1 REPLY

Re: Filter consecutive records that have same value as preceding record

Here it is...

sel

csum(1,c1)

,con_rec.*

,min(c2) over (rows between 1 preceding and 1 preceding) as a

, min(c3) over (rows between 1 preceding and 1 preceding) as b

,case when c2=a and c3=b then 'n'else 'y' end as res from con_rec

qualify res='y';