MySQL-Count consective number-CodePudding

Write a SQL query to find number position as well number and consective number count

CREATE TABLE Logs (
  `Id` INTEGER,
  `Num` INTEGER
);

INSERT INTO Logs
  (`Id`, `Num`)
VALUES
  ('1', '1'),
  ('2', '1'),
  ('3', '1'),
  ('4', '2'),
  ('5', '1'),
  ('6', '2'),
  ('7', '2');

Prefere Return

StartId Num Count
1       1   3
4       2   1
5       1   1
6       2   2

and also can i get any suggestion which function can be use with case function in MySQL Function

CodePudding user response：

Looking at your data and expected results, I believe your expectations are inconsistent, eg you can either have 1 and 6 or 3 and 7.

What you need to do is group the data by successive num values and aggregate the results.

with gp as (
    select *, 
        Row_Number() over(order by id)
        - Row_Number() over(partition by num order by id) g
    from logs
)
select Min(id) Id,
    num, Count(*) Count
from gp
group by g, num
order by id