Is there an extension to sqlite the provide window functions such as
row_number over and partition? There are plenty of googles seeking said
functionality.
What am I looking to do? Using a table X, I've built a summary table,
calculating a value called net_non_pieces. net_not_pieces is a qty of rows
from a group rows that needs to be recoded to a different value. So what I
want to do is for each group in X, look up the calculated value in
crrt_net_non, then grab the first net_non_pieces (sequenced, of course) and
apply a code to those rows, leaving the remaining rows alone.
I'm not sure how to accomplish such a thing
CREATE TABLE crrt_net_non -- developed from table X
(
zip TEXT, --composite key
crrt TEXT, --composite key
pkg_pieces integer, --total row in group
sel_pieces integer,
non_pieces integer,
net_non_pieces integer --qty of this group to recode
);
Its a pretty innocent thing to do with window functions. It is not trivial
to do it without. I can't even figure out how.
Why am I not dumping Sqlite and going with prostgres or the like? Because
sqlite is awesome. It is a portable file, easily created as I need for
each project, that can be moved around or deleted, making it super easy to
maintenance in my production environment. Love the thing. But this
problem has me flummuxed.
Thanks for your time and consideration,
dvn
