i have doubt in sql server query
i have table dbo.[files] columns id, filename, filesize(mb)
id filename filesize(mb) ---------------------------- 1 aa.gif 3 2 bb.gif 5 3 cc.gif 7 4 dd.gif 5 5 ee.gif 2 6 fff.gif 4 7 gg.gif 3 my requirement is, have group maximum file size limit , give group number group having sum of filesize less or equal maximum limit
suppose maximum file size sum 10
example result:
id filename filesize(mb) groupno -------------------------------------- 1 aa.gif 3 1 2 bb.gif 5 1 5 ee.gif 2 1 3 cc.gif 7 2 7 gg.gif 3 2 4 dd.gif 5 3 6 fff.gif 4 3 sum of filesize in group 10
final result required
groupno ids -------------- 1 1,2,5 2 4,6 3 3,7
i tried quite bit, in end had settle procedural solution, albeit in t-sql. following script works on temporary table @t1 (columns: id, fn, size, gr) data has copied first. alternatively can equip original table additional group-id column gr , work on table.
declare @g int=1, @cnt int,@si int; -- group-no, row-count, size of group select @cnt=count(*) #t1 gr=0; while (@cnt>0) begin select @si=isnull(sum(size),0) #t1 gr=@g; update #t1 set gr=@g id = ( select top 1 id #t1 gr=0 , @si+size<=10 order @si+size desc); if (@@rowcount=0) begin if (@si=0) update top(1) #t1 set gr=@g gr=0; -- files > 10mb set @g=@g+1; end; select @cnt=count(*) #t1 gr=0 end; select * #t1 a working example can found @ http://data.stackexchange.com/stackoverflow/query/337062/group-into-10mb-chunks-2
Comments
Post a Comment