How to identify and select ROW, where there was a change in value in specific column

Database

How to identify and select ROW, where there was a change in value in specific column

Hello,

--My apologies for my english

Please, I'm looking for a good way, how to identify first row from behind, before change in column TYPE_CD

When you look below on examples, you will understand, what I am looking for. 

-->Standalone tables (5 examples)

ID TYPE_CD PER_DT CONTRCT

100 4 ('2001-02-22 00:00:00.000000', '2009-12-12 16:33:10.000000') 443064

100 3 ('2009-12-12 16:33:10.000000', '2009-12-22 11:14:18.000000') 159906

100 4 ('2009-12-22 11:14:18.000000', '2010-04-21 15:58:14.000000') 160179

100 3 ('2010-04-21 15:58:14.000000', '2010-05-10 13:00:19.000000') 166865 --  That's row what I need. 

100 3 ('2010-05-10 13:00:19.000000', '2010-11-10 22:02:32.000000') 16730

100 3 ('2010-11-10 22:02:32.000000', '2011-02-10 18:52:27.000000') 178815

100 3 ('2011-02-10 18:52:27.000000', '2011-05-24 20:25:31.000000') 182230

100 3 ('2011-05-24 20:25:31.000000', '2014-11-04 23:57:16.000000') 187307

100 3 ('2014-11-04 23:57:16.000000', '2015-06-30 08:27:36.000000') 116704

100 3 ('2015-06-30 08:27:36.000000', '2015-09-05 18:59:06.000000') 125744

100 3 ('2015-09-05 18:59:06.000000', '9999-12-31 23:59:59.999999') 129191

ID TYPE_CD PER_DT CONTRCT

100 3 ('2009-12-12 16:33:10.000000', '2009-12-22 11:14:18.000000') 159906

100 4 ('2009-12-22 11:14:18.000000', '2010-04-21 15:58:14.000000') 160179

100 3 ('2010-04-21 15:58:14.000000', '2010-05-10 13:00:19.000000') 166865 --  That's row what I need. 

100 3 ('2010-05-10 13:00:19.000000', '2015-06-30 08:27:36.000000') 16730

100 3 ('2015-06-30 08:27:36.000000', '2015-09-05 18:59:06.000000') 125744

100 3 ('2015-09-05 18:59:06.000000', '9999-12-31 23:59:59.999999') 129191

ID TYPE_CD PER_DT CONTRCT

100 3 ('2010-04-21 15:58:14.000000', '2010-05-10 13:00:19.000000') 166865 --  That's row what I need. 

100 3 ('2010-05-10 13:00:19.000000', '2015-06-30 08:27:36.000000') 16730

100 3 ('2015-06-30 08:27:36.000000', '2015-09-05 18:59:06.000000') 125744

100 3 ('2015-09-05 18:59:06.000000', '9999-12-31 23:59:59.999999') 129191

ID TYPE_CD PER_DT CONTRCT

100 4 ('2010-04-21 15:58:14.000000', '2010-05-10 13:00:19.000000') 166865  

100 3 ('2015-09-05 18:59:06.000000', '9999-12-31 23:59:59.999999') 129191 --  That's row what I need.

ID TYPE_CD PER_DT CONTRCT

100 3 ('2015-09-05 18:59:06.000000', '9999-12-31 23:59:59.999999') 129191 --  That's row what I need. 

I did succesfully query with preceding value to separate column and then made huge CASE statment, which select value from right rows. 

But I don't think it is good solution, because I will have to run this process frequently in the future on 30 milions of rows. 

Thank you for advise

Galad

Tags (1)
2 REPLIES
N/A

Re: How to identify and select ROW, where there was a change in value in specific column

Hi Galad,

I don't think there's a way without nested OLAP, e.g.

SELECT *
FROM
(
SELECT *
FROM tab
QUALIFY -- start with the latest row and go on as long as the TYPE_CD doesn't change
MIN(type_cd) OVER (PARTITION BY D ORDER BY per_dt DESC ROWS UNBOUNDED PRECEDING) =
MAX(type_cd) OVER (PARTITION BY D ORDER BY per_dt DESC ROWS UNBOUNDED PRECEDING)
) AS dt
QUALIFY -- find the oldest row
ROW_NUMBER() OVER (PARTITION BY D ORDER BY per_dt ASC) = 1

On (only) 30 million rows this should be resonable fast...

Re: How to identify and select ROW, where there was a change in value in specific column

Hi Dieter,

Thank you, it helped me a lot. I didnt know about "unbounded" syntax. 

Galad