July 18, 2005 at 2:51 am
Hi All,
Does anyone know if it is possible to return a set size of data from a table ?
For example, a table that holds binary data represented by:
create table packets(ident int identity, data varchar(100), messageid int)
insert into packets(data) values('zz')
insert into packets(data) values('zzzzzz')
insert into packets(data) values('zzzzzzz')
insert into packets(data) values('zzzzzzz')
insert into packets(data) values('zzzzzzzz')
insert into packets(data) values('zzzzzzzz')
insert into packets(data) values('zzzzzzzzz')
insert into packets(data) values('zzzzzzzzzz')
what i need is all the rows up to where the sum of the datalength of data is less that 16 for the whole set. whihc should be rows 1,2 and 3
so far i've tried grouping but can't get a restriced resultset back.
many thanks in advance
cheers
dbgeezer
July 18, 2005 at 4:19 am
I assume that ident is int identity(1,1) and that you want the largest number n such that the sum of the datalength of all rows with ident <= n is < 16. Then it goes:
select max(p1.ident) from packets p1 where p1.ident in
(select p2.ident from packets p2, packets p3 where p3.ident <= p2.ident
group by p2.ident
having sum(len(p3.data)) < 16
)
which returns 3. If you want 1, 2, 3 back, then it is sufficient to use the subquery.
July 18, 2005 at 4:24 am
thank you Jesper, that's exactly what we need.
i must have thries everything except that ! thanks again.
cheers
dbgeezer
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply