-1

I read a lot about MySQL gaps and islands problem, but I cannot find anything enough closer to understand my problem. I have gaps from zeros and islands from 15. You can see what I am talking about in the following tables The first table is my data:

CREATE TABLE gapsandislands (
  rownum int(11) NOT NULL,
  integer_id int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO gapsandislands (rownum, integer_id) VALUES
(1, 0),
(2, 0),
(3, 0),
(4, 0),
(5, 15),
(6, 15),
(7, 15),
(8, 15),
(9, 15),
(10, 15),
(11, 15),
(12, 15),
(13, 0),
(14, 0),
(15, 0),
(16, 0),
(17, 0),
(18, 0),
(19, 0),
(20, 0),
(21, 15),
(22, 15),
(23, 15),
(24, 15),
(25, 0),
(26, 0),
(27, 0);

My islands in this example are 5-12 and 21-24. But how can I managed them in a new table?

Даяна Димитрова
  • 93
  • 10
  • Do not post [images](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question) of data, sample data should be *consumable text* in your question, ideally as *create* and *insert* statements, or alternatively a [DB<>Fiddle](https://dbfiddle.uk/). See the [question guide](https://stackoverflow.com/help/how-to-ask). – Stu Feb 18 '22 at 08:46
  • CANT READ YOUR IMAGES –  Feb 18 '22 at 08:50
  • *how can I managed them in a new table* - What esactly is your question here? – Stu Feb 18 '22 at 09:03
  • Is this working towards a solution to the previous question you posted? Or independent of it? – P.Salmon Feb 18 '22 at 09:03

1 Answers1

1

if I got it right, this will help you

with no_zeroes as (
select rownum, integer_id, rownum - row_number() over(order by rownum) gaps_detector
  from gapsandislands
 where integer_id > 0
 )
 
select min(rownum) start_ruwnum, max(rownum) end_rownum
  from no_zeroes
 group by gaps_detector

In case you're on mysql version below 8 that don't support CTEs (the "with" part), copy query from the into the "from" part as a subquery

select min(rownum) start_ruwnum, max(rownum) end_rownum
  from (select rownum, integer_id, rownum - row_number() over(order by rownum) gaps_detector
          from gapsandislands
         where integer_id > 0)no_zeroes
 group by gaps_detector

Now, let's go to what's to be done in order to crack the problem you're facing.

  1. Get rid of zeroes as you don't need them in the output anyway.
  2. All you need to crack this problem is some value that will change from island to island. That's what "rownum - row_number()" is here for.

Do not hesitate to ask if you still have questions

Here's dbfiddle to check

ekochergin
  • 4,109
  • 2
  • 12
  • 19
  • Thank you very much!! That is exactly what I was looking for! It solves my problem perfectly! – Даяна Димитрова Feb 18 '22 at 11:20
  • Do you have any idea how can I convert one row with 96 columns to 96 rows with one column? Because I tried to ask my question more clearly and I did not mention that the data is in row and now I should convert it to col in order to use your solution. – Даяна Димитрова Feb 18 '22 at 11:46
  • 1
    @ДаянаДимитрова that's what they call "pivot" or "pivoting" and unfortunately mysql doesn't support that thing. You'll have to implement it yourself. Just google for "mysql pivot". (for 96 cols it will be a pain in some part of anatomy) – ekochergin Feb 18 '22 at 11:56