sql Select the max value within a combination of starting time and ending time

I have a database as the data table below. Its a standard Open High Low Close Database. I wanna select the date, start-time, end-time, max([high] over ***The period of time) , and Close

Data Table

date	time	high
20100104	94000	21850
20100104	94500	21903
20100104	95000	21875
20100104	95500	21855
20100104	100000	21904
20100104	100500	21922
20100104	101000	21935
20100104	101500	21949
20100104	102000	21954
20100104	102500	21987
20100104	103000	22017
20100104	103500	22049
20100104	104000	22024
20100104	104500	22015
20100104	105000	21988
20100104	105500	21996
20100104	110000	21966
20100104	110500	21989
20100104	111000	21983
20100104	111500	21942
20100104	112000	21937
20100104	112500	21925
20100104	113000	21927
20100104	113500	21956
20100104	114000	21950
20100104	114500	21904
20100104	115000	21898
20100104	115500	21896
20100104	120000	21915
20100104	120500	21900
20100104	121000	21850
20100104	121500	21845
20100104	122000	21856
20100104	122500	22000
20100104	142500	21855
20100104	143000	21900
20100104	143500	21839
20100104	144000	21758
20100104	144500	21751
20100104	145000	21763
20100104	145500	21786
20100104	150000	21804
20100104	150500	21794
20100104	151000	21803
20100104	151500	21814
20100104	152000	21850
20100104	152500	21835
20100104	153000	21853
20100104	153500	21874
20100104	154000	21900
20100104	154500	21842
20100104	155000	21833
20100104	155500	21819
20100104	160000	21861
20100104	160500	22000
20100104	161000	22000
20100105	94000	22079
20100105	94500	22095
20100105	95000	22095
20100105	95500	22098
20100105	100000	22109
20100105	100500	22089
20100105	101000	22061
20100105	101500	22033
20100105	102000	22045
20100105	102500	22103
20100105	103000	22071
20100105	103500	22092
20100105	104000	22093
20100105	104500	22184

Result

date	start-time	end-time	high among the period
20100104	94000	94000	21850
20100104	94000	94500	21903
20100104	94000	95000	21903
20100104	94000	95500	21903
20100104	94000	100000	21904
20100104	94000	100500	21922
20100104	94000	101000	21935
20100104	94000	101500	21949
20100104	94000	102000	21954
…	…	…	…
…	…	…	…
20100104	94500	94500	21903
20100104	94500	95000	21903
20100104	94500	95500	21903
20100104	94500	100000	21904
20100104	94500	100500	21922
20100104	94500	101000	21935
20100104	94500	101500	21949
20100104	94500	102000	21954
…	…	…	…
…	…	…	…
20100105	94000	94000	22079
20100105	94000	94500	22095
20100105	94000	95000	22095
20100105	94000	95500	22098
20100105	94000	100000	22109
20100105	94000	100500	22109
20100105	94000	101000	22109
20100105	94000	101500	22109
20100105	94000	102000	22109
…	…	…	…
…	…	…	…
20100105	94500	94500	22095
20100105	94500	95000	22095
20100105	94500	95500	22098
20100105	94500	100000	22109
20100105	94500	100500	22109
20100105	94500	101000	22109
20100105	94500	101500	22109
20100105	94500	102000	22109
…	…	…	…
…	…	…	…
…	…	…	…
…	…	…	…

How should i write this?

Answer

Self-join the table to get all start and end times. Then select the maximum high in a subquery in the select clause.

select
  s.date, s.time as start_time, e.time as end_time,
  (
    select max(high)
    from mytable t
    where t.date = s.date
    and t.time between s.time and e.time
  ) as max_high
from mytable s
join mytable e on e.date = s.date and e.time >= s.time
order by s.date, s.time, e.time;