-1

I have a problem. I want to write oracle query the database which, based on the data from the image

enter image description here

will create the result:

START_DATE         |END_DATE           |COLOR
-------------------|-------------------|------
2017-05-25 15:39:39|2017-05-25 15:41:06|GREEN
2017-05-25 15:41:06|2017-05-25 15:53:27|ORANGE
2017-05-25 15:53:27|2017-05-25 15:57:16|GREEN
2017-05-25 15:57:16|2017-05-25 15:59:24|YELLOW

etc...

Aleksej
  • 22,443
  • 5
  • 33
  • 38
karolina.ss
  • 25
  • 7
  • 2
    Please post data as formatted text, [not screenshots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). Also, what have you tried so far? – Aleksej May 26 '17 at 11:12

1 Answers1

3

You are trying to find adjacent values. One method uses a difference of row numbers:

select min(start_date) as start_date, max(start_date) as end_date, color
from (select t.*,
             row_number() over (order by start_date) as seqnum,
             row_number() over (partition by color order by start_date) as seqnum_c
      from t
group by (seqnum - seqnum_c), color;

It is a bit challenging to explain why the difference of row numbers works. I encourage you to run the subquery and to stare at the numbers. You should be able to see why the difference is constant for adjacent color values.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This technique is know as [tabibitosan](http://rwijk.blogspot.co.uk/2014/01/tabibitosan.html) – Boneist May 26 '17 at 11:31
  • Rather than answering the same question for the umpteenth time - next time you could go back through your previous answers and find a duplicate and mark the question as such. – MT0 May 26 '17 at 11:35
  • Referring to your answer, something is wrong. I do not have anything like the end date, in the database I have records only about the starting date. I make a difference (lag) between the lines to see how long it takes to make some detail on the machine. In addition, "group by" is to whole query or just select "t"? – karolina.ss May 26 '17 at 12:50
  • @karolina.ss looks like Gordon typo'd and it should be `max(start_date)`. The group by applies to the outer query; you can't do it in the inner subquery because of the analytic functions (`row_number() over ..`). – Boneist May 26 '17 at 14:37
  • @Boneist, Thank you for explaining. Using Gordon Linoff example, I have data: START_DATE| END_DATE| COLOR ------ | ------ | ------ 2017-05-25 17:19:11| 2017-05-25 17:31:42| GREEN 2017-05-25 17:38:20| 2017-05-25 18:01:00| GREEN 2017-05-25 14:12:10| 2017-05-25 14:12:10| YELLOW 2017-05-25 18:22:34| 2017-05-25 19:02:56| GREEN And it is not correct because of intervals.. I would like the end date to be the start date for the next row. It means: START|END|KOLOR 2017-05-25 14:01:25|2017-05-25 14:07:39|GREEN 2017-05-25 14:07:39|2017-05-25 14:12:10|YELLOW – karolina.ss May 29 '17 at 06:47
  • @karolina.ss ok, so instead of using `max(start_date)` you need to use the `lead()` analytic function. In future, next time you ask a question, please ensure that you put all relevant details in your question, including text representation of your input data as well as your expected output, and an explanation of the logic that generates the output data. That way, you help us to be able to help you better. – Boneist May 30 '17 at 08:33