-3

I have the following table

create table Launches (Id int, Name char)

insert into Launches values
(1, 'A'),
(2, 'A'),
(3, 'B'),
(4, 'B'),
(5, 'B'),
(6, 'B'),
(7, 'C'),
(8, 'B'),
(9, 'B')

The result should be

4 - B

From 3 to 6

Similar question -

Count Number of Consecutive Occurrence of values in Table

Joe Jobs
  • 201
  • 1
  • 12
  • 1
    What have you tried? Where did you get stuck? Please show us your research and attempt. – Dale K Dec 23 '20 at 23:40
  • The question you have linked to tells you how to do it? – Dale K Dec 23 '20 at 23:50
  • It doesn't because it does not report where the biggest consecutive sequence starts and ends. In my real life case the letter "B" repeats many times in many places and without the indexes I can't localize the biggest sequence. – Joe Jobs Dec 26 '20 at 09:45

1 Answers1

2

You can subtract an enumerated value for each name to get a constant for adjacent values that are the same. The rest is aggregation:

select top (1) name, count(*), min(id), max(id)
from (select l.*,
             row_number() over (partition by name order by id) as seqnum
      from #Launches l
     ) l
group by (id - seqnum), name
order by count(*) desc;

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • That is a great script! However for me is equally important to know the first and the last index - 3 and 6 in this example – Joe Jobs Dec 23 '20 at 23:46
  • 1
    @JoeJobs . . . Just include that in the `select`. – Gordon Linoff Dec 23 '20 at 23:47
  • I have no idea how to do that, really – Joe Jobs Dec 24 '20 at 00:06
  • 1
    @JoeJobs Gordan has already done it for you - check his edits. However it sounds like you might benefit more from checking out some SQL tutorials and learning the basics, because sure people will answer your questions here, but you won't gain the skills to do it yourself that way. – Dale K Dec 24 '20 at 00:10
  • Thanks, I cant edit the answer, it should be "Launches" instead of "#Launches" – Joe Jobs Dec 24 '20 at 00:58
  • It says I have to change at least 6 characters in order to be able to save it. And yes I upvoted the answer. I was told to wait a few days until accepting an answer, to encourage more users to answer the question – Joe Jobs Dec 24 '20 at 01:28
  • "Oops! Your answer couldn't be submitted because: Edits must be at least 6 characters; is there something else to improve in this post?" – Joe Jobs Dec 24 '20 at 01:30
  • 1
    @JoeJobs . . . I don't understand. The question uses `#Launches`. The code in the fiddle is slightly different, but it really is not hard to change the table name in a query. – Gordon Linoff Dec 24 '20 at 02:14
  • My bad, I copied from the other question, now I replaced #Launches with Launches in the question. The "#" ony complicates things, it is better without it. I just wanted to make sure that copying the code in that fiddle it works without modification. – Joe Jobs Dec 24 '20 at 02:19