Lalit Lalit - 2 months ago 9
SQL Question

Select Every Nth Record From SQL

I am having a Database in which data is been logged in regular interval of time i e for 5 minutes say it is been logged for 24 hours as shown in below table.

Date and Time Value
2016-09-17 14:00:00 25.26
2016-09-17 14:05:00 24.29
2016-09-17 14:10:00 25.22
2016-09-17 14:20:00 25.10





2016-09-17 23:55:00 20.21


I want To display Every 1 hour reading using SQL query There are chances the some reading may be missing The expected Output should be.

Date and Time Value
2016-09-17 14:00:00 25.26
2016-09-17 15:00:00 27.29
2016-09-17 16:00:00 28.12
2016-09-17 17:00:00 22.11


There are chances my be that some reading may be missing. like

Date and Time Value
2016-09-17 14:35:00 25.26


This reading may be missing
Please Suggest SQL query for the same

Answer
SELECT t1.DateCol,
       t1.Value
FROM yourTable t1
INNER JOIN
(
    SELECT MIN(DateCol) AS firstDate
    FROM yourTable
    GROUP BY FORMAT(DateCol, 'dd/MM/yyyy hh')
) t2
    ON t1.DateCol = t2.firstDate

If you instead wanted to group by every 15 minutes, you could try:

GROUP BY CONCAT(FORMAT(DateCol, 'dd/MM/yyyy hh'),
                FLOOR(DATEPART(MINUTE, DateCol) / 15))
Comments