how to filter database data in sql server by per hour

if i have the data like the picture above, let’s say 1 day data per minute, and i want to filter it to be per hour, so i will have 24 data, because i take the data per hour. how to do that

i have tried some queries, like this one using group by, but the result is not like i want. didn’t work for grouping the datas.

   SELECT myDatetime FROM datates
  WHERE myDatetime >= '2020-03-01 05:30:00'
   AND myDatetime < DATEADD(DAY,1,'2020-03-01 07:30:00')
   group by myDatetime ,DATEPART(hour,myDatetime )

Answer

You can use row_number(). For instance, if you want the first value per hour:

select d.*
from (select d.*,
             row_number() over (partition by convert(date, myDatetime), datepart(hour, myDatetime) order by mydatetime) as seqnum
      from datates d
     ) d
where seqnum = 1;

If you want to sum the values, use aggregation:

select dateadd(hour, datepart(hour, myDatetime), convert(datetime, convert(date, myDatetime))),
       sum(nilai)
from datates d
group by dateadd(hour, datepart(hour, myDatetime), convert(datetime, convert(date, myDatetime)));