# how to select date time step by 60 second from first value to last value in table

I want to add 60 seconds to the date value from the first date and return the data if it exists in that interval like this

datetime value
2021-05-02 12:20:1 5
2021-05-02 12:20:15 8
2021-05-02 12:20:26 12
2021-05-02 12:21:1 63
2021-05-02 12:21:23 90
2021-05-02 12:21:12 73
2021-05-02 12:22:3 39
2021-05-02 12:23:12 3
2021-05-02 12:28:23 74
2021-05-02 12:28:26 38
2021-05-02 12:28:26 72
2021-05-02 12:28:28 12
2021-05-02 12:28:30 69

RESULT like :

datetime value
2021-05-02 12:20:1 5
2021-05-02 12:21:1 63
2021-05-02 12:22:3 39
2021-05-02 12:23:12 3
2021-05-02 12:28:23 74

I try by this code But I did not get the right Result

``````        WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY datetime ORDER BY DATETIME)-  ROW_NUMBER() OVER (PARTITION BY dateadd(second,60,datetime) ORDER BY DATETIME) as g
FROM Inputs
)
SELECT DateTime
FROM cte
group by g
``````
• Can you construct a fiddle (dbfiddle.uk) with your tables and data - also, a more detailled explanation of your requirement would also help! Some of the data points aren't even real times! Sep 13, 2021 at 14:37
• Take a look at minimal reproducible example and add the requisite details to your question. Sep 13, 2021 at 15:11

You can truncate the date to the minute and partition by that value

``````WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATEADD(minute, DATEDIFF(minute, 0, t.datetime), 0)
ORDER BY t.datetime) AS rn
FROM YourTable t
)
SELECT
datetime,
value
FROM cte
WHERE rn = 1;
``````
• HOW TO PARTITION BY 15 MINUTE OR 10 MINUTE? Sep 14, 2021 at 5:15
• `DATEADD(minute, DATEDIFF(minute, 0, t.datetime) / 15 * 15, 0)` Sep 14, 2021 at 9:30