Get First Record of Each Group

First I would like to apologize if it is a basic question.

So, i have monitoring data being stored every 5 seconds. I want create a query that returns me the first record every 10 minutes, for example:

|Data                    |  Voltage (V) |
|2020-08-14 14:00:00     |     10
|2020-08-14 14:00:05     |     15
|2020-08-14 14:00:00     |     12
....                     |
|2020-08-14 14:10:10     |     25
|2020-08-14 14:10:15     |     30
|2020-08-14 14:10:20     |     23

The desired result is:

|Data                  |Voltage (V)  |
|2020-08-14 14:00:00        10       |
|2020-08-14 14:10:10        25       |

I’m using SQLServer database.

I read about similar solutions as post: Select first row in each GROUP BY group?

But i can’t resolve my issue.

I started with:

SELECT Data, Voltage
GROUP BY DATEADD(MINUTE,(DATEDIFF(MINUTE, 0 , Data)/10)*10,0)
ORDER BY DATA DESC

But i can’t use FIRST() or top 1 in this query.

Anyone have ideas?

Thanks a lot!

Answer

If I understand correctly:

select t.*
from (select t.*,
             row_number() over (partition by DATEADD(MINUTE,(DATEDIFF(MINUTE, 0 , Data)/10)*10,0) order by data) as seqnum
      from t
     ) t
where seqnum = 1;

Advertisement

Answer