Table crrt_net_non_pieces: zip crrt pkg_pieces sel_pieces non_pieces net_non_pieces 53001 R501 52 6 46 42 53001 R502 34 7 27 24
Addresses (as I have been referring to as X) is going to have too many rows (86 for this example, 52 rows for 53001.R501 and 34 rows for 53001.R502. The calculation result says I need 42 of the 52 X rows from 53001.R501 to be recoded and 24 of the 34 X rows from 53001.R502. Addresses table: zip crrt segment version_id 53001 R501 06 0060 53001 R501 06 0060 53001 R501 06 0060 53001 R501 06 0060 53001 R501 06 0060 In sql server, I would be doing something like this: select x.* ,'xx' as segment from ( select -- count(1) a.zip ,a.crrt ,a.version_id ,ROW_NUMBER() OVER ( PARTITION BY a.zip,a.crrt ORDER BY CASE WHEN a.version_id = '0060' THEN 0 WHEN a.version_id = '0064' THEN 2 WHEN a.version_id = '0061' THEN 3 ELSE 99 END ASC ) AS NUM from dbwork..addresses a )x join dbwork..crrt_net_non y on y.zip = x.zip and y.crrt = x.crrt where x.num <= y.net_non_pieces ; And the results would come out something like the following: I truncate the rows with num values 1..whatever. Notice NUM is the sequential value applied to the input (1..52 in the case of R501 and 1..34 in the case of R502). I then only kept the number of rows as identified by the corresponding y.net_non_pieces value. zip crrt version_id NUM segment [truncated num 1..38] 53001 R501 0060 39 xx 53001 R501 0060 40 xx 53001 R501 0060 41 xx 53001 R501 0060 42 xx [truncated num 1..24] 53001 R502 0060 21 xx 53001 R502 0060 22 xx 53001 R502 0060 23 xx 53001 R502 0060 24 xx Is this better? Sorry that I imagined/assumed that you guys just picture this stuff in your head. I get that impression reading this list. dvn These window functions seem to be some sort of Holy Grail that I just can't imagine duplicating without it. On Wed, Oct 14, 2015 at 4:27 PM, R.Smith <rsmith at rsweb.co.za> wrote: > > > On 2015-10-14 11:20 PM, Don V Nielsen wrote: > >> X has columns zip & crrt, just like crrt_net_non. These form a composite >> key identifying groups within x. A value "53001.R501" would be an >> example...53001 being the zip code and R501 being the carrier route. >> There >> are 52 rows in X that have the key 53001.R501. A calculation determined >> that I need 42 rows from that key and saved the result in crrt_net_non, >> the >> row looking like "53001.R501.52.6.46.42". What I need is a sql function >> that can iterate over crrt_net_non, then grab the rows from X, >> "53001.R501" >> being first key, sort them into an internal group sequence, then update a >> code of the first 42 rows of that sorted group, and then doing this until >> crrt_net_non is exhausted. >> > > Hi Don, usually a CTE can be pressed into service to make things work. > Maybe others know exactly what you mean here but it's a bit Greek to me. > > As Igor suggested - perhaps some data to accompany this schema and an > example result-set (or resulting updated table) from it will make it clear > what you want to achieve with the query. Feel free to simplify so the > essence of what is needed remain, but be sure to give an example that > cannot be achieved by other arbitrary means. > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
