-1

I have this table :

enter image description here

I want to find minimum date_time value for each bunch of colored record, that is, the result of the table will be this table.

enter image description here

Which SQL command is needed?

mehdi hallaj
  • 1
  • 3
    Gaps and island first then windowed function/group by. Easy to achieve if you provide https://dbfiddle.uk data. – Lukasz Szozda Oct 21 '18 at 10:17
  • 1
    You show photos of Excel. Do you have actual SQL tables? If so, please show the table definition of the table. Please also show the data you have as text. For SQL, the query would be `select Strand1, min(DATE_TIME) from mytable group by Strand1` – Corion Oct 21 '18 at 10:17
  • 1
    @Corion Won't work you have multiple `0` groups – Lukasz Szozda Oct 21 '18 at 10:18
  • 1
    First you need to get subgrouping per each continuous `000`,`111`,`000`,... then you could use `GROUP BY`. – Lukasz Szozda Oct 21 '18 at 10:20
  • 2
    @lukaszSzozda: Aaah, yes, now I understand the "colouring". Yes, a window function is adequate then ;) – Corion Oct 21 '18 at 10:20
  • @mehdihallaj . . . Please tag your question with the database you are using. – Gordon Linoff Oct 21 '18 at 11:48

2 Answers2

1

This is sort-of a gaps-and-islands problem. But the simplest way to think of it is as a simple lag():

select t.*
from (select t.*,
             lag(t.strand1) over (order by t.date_time) as prev_strand1
      from t
     ) t
where prev_strand1 is null or prev_strand1 <> strand1;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

If you want to find minimum of date_time value for each whatever value of Strand1

select Strand1, min(DATE_TIME) from mytable where Strand1 = whatever group by Strand1
Pvria Ansari
  • 406
  • 4
  • 20