Keith Medcalf, on Monday, January 13, 2020 08:03 PM, wrote... > > > And this version is several times faster since only the changes are > union'd which minimizes the total number of records processed. > The index should be "create index i on t (a, idate);" Because of the way > indexes work, entries on the same a, idate will be ordered by n. (though > really idate should be unique within a, so the index should be a unique > index) > > select a, idate, variable, oldv, newv > from ( > select a, > idate, > 'b' as variable, > lag(b, 1) over (partition by a order by idate) as oldv, > b as newv > from t > order by a, idate > ) > where oldv <> newv > union all > select a, idate, variable, oldv, newv > from ( > select a, > idate, > 'c' as variable, > lag(c, 1) over (partition by a order by idate) as oldv, > c as newv > from t > order by a, idate > ) > where oldv <> newv > union all > select a, idate, variable, oldv, newv > from ( > select a, > idate, > 'd' as variable, > lag(d, 1) over (partition by a order by idate) as oldv, > d as newv > from t > order by a, idate > ) > where oldv <> newv > union all > select a, idate, variable, oldv, newv > from ( > select a, > idate, > 'e' as variable, > lag(e, 1) over (partition by a order by idate) as oldv, > e as newv > from t > order by a, idate > ) > where oldv <> newv > order by a, idate, variable; >
Wow! Thanks, Keith. Yes, this one is much faster. As Ryan suggested, I will use this one. Thank so much. I really appreciate the help. I already have an index on (a,idate). I hope that I will be like you guys someday. ;-) Thanks again. josé _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
