Hi,

Given the following dataset, how can I determine the maximum number of
consecutive Bs for a given A?

A B
- -
2 1
2 2
2 7
3 2
3 3
3 4
3 6
3 8
3 9
3 10
3 13
3 14
3 15
3 16
4 1
4 3
4 5
4 6
4 7
4 8
4 10
5....

So the output should resemble:

A MAX Count for B
-  -
2 2 -- for 1 2
3 4 -- for 13 14 15 16
4 1 -- no consecutive numbers
5...

Thanks

Jerry

Homework assignment?

Tom Dacon
Dacon Software Consulting

Show quoteHide quote

untested, as I'm at home:
select a, max(maxb - b + 1)
from(select a, b, (select max(b) from t t1 where t.a=t1.a and t1.b=t.b+
(select count(*) from t t2 where t2.a=t1.a and t.b<t2.b and t2.b<t1.b)
) maxb from t) t
group by a

could be easier with row_number()

I googled up:
Jerry Spivey MCT, MCSE, MCSD Senior SQL

Homework...nah...question from a friend.

Yeah...MCDBA, MCP...just too many letters! ;-)

Good question though :-)
Show quoteHide quote

If my friend's enemy is my enemy, then is my friend's homework my homework?

ML

untested, as I'm still at home and bored:

assuming combination (a,b) is unique

select s1.a, max(s2.ctb-s1.ctb+1)
from
(select a, b, (select count(*) from tb t2 where t1.a=t2.a and
t1.b<=t2.b) ctb from tb t1 ) s1,
(select a, b, (select count(*) from tb t2 where t1.a=t2.a and
t1.b<=t2.b) ctb from tb t1 ) s2
where s1.a=s2.a
and (s2.b-s1.b)=(s2.ctb-s1.ctb)
group by s1.a

not sure what was wrong with the previous one, maybe because I used t
twice, both as the table name and as an alias.

another one:

select a, max(bmin - b +1) from
(
select left_end.a,left_end.b, min(right_end.b) bmin
from
(
select a, b where not exists(
select 1 from tb t1
where t1.a=t.a
and (t1.b+1)=t.b
from tb t)
) left_end,
(
select a, b where not exists(
select 1 from tb t1
where t1.a=t.a
and (t1.b-1)=t.b
from tb t)
) right_end
where left_end.a=right_end.a
and left_end.b<=right_end.b
group by left_end.a, left_end.b) intervals

Alexander,

Thanks for the post.  The untested query yeilded the following resultset:

1 1
2 1
3 1
4 1
5 1
6 1

Any other queries to try?

Thanks

Jerry
Show quoteHide quote

On Fri, 11 Nov 2005 11:26:19 -0800, Jerry Spivey wrote:

Hi Jerry,

Why should the series (4 5)/(4 6)/(4 7)/(4 8) not be reported as a
consecutive series? An error in your post, I presume.

The following is untested. Check out www.aspfaq.com/5006 if you prefer a
tested reply.

SELECT   A, MAX(last - first)
FROM    (SELECT     f.A, f.B AS first, MIN(l.B) AS last
         FROM       YourTable AS f
         INNER JOIN YourTable AS l
               ON   l.A = f.A
               AND  l.B >= f.B
         WHERE NOT EXISTS
          (SELECT   *
           FROM     YourTable AS b
           WHERE    b.A = f.A
           AND      b.B = f.B - 1)
         AND NOT EXISTS
          (SELECT   *
           FROM     YourTable AS a
           WHERE    a.A = f.A
           AND      a.B = l.B + 1)
         GROUP BY   f.A, f.B) AS seq
GROUP BY A

Best, Hugo

Random number generation with seed value
Compute sum of count(*) with group by
Copying records from one table to another
Automate Importing File
concurrency in stored proc