Clever Geek Handbook

SQL: how to query to select only the first row every hour?

I am trying to find how to fetch the first row of every hour from my recorders that write data to the database every minute.

my example data:

Val     Date_time
1734618 2017-06-09 14:01:04
1734609 2017-06-09 14:00:05
1734601 2017-06-09 13:59:04
1734593 2017-06-09 13:58:04
...
1734127 2017-06-09 13:02:04
1734119 2017-06-09 13:01:04
1734111 2017-06-09 13:00:05
1734103 2017-06-09 12:59:04

My problems: I don’t know how to query only the first row from temporary data every hour, and I don’t know why I can’t use the DATE_FORMAT () function in Microsoft SQL Server control, this function in text autocomplete does not exist at all. I found that everyone fetches every minute it doesn't work for me due to the lack of a DATE_FORMAT () function.

SELECT * 
FROM table 
WHERE Date_time IN ( SELECT MIN(Date_time) AS Date_time
              FROM table
              GROUP BY DATE_FORMAT(Date_time,'%Y-%m-%d %H:%i'))

my example: 2 lines

Val     Date_time
1734609 2017-06-09 14:00:05
1734111 2017-06-09 13:00:05

Any ideas please help sir? Thank you so much for your help.

edited: With FORMAT () function it works.

+3


source to share


5 answers


Don't use solutions that use subsamples, performance will suffer and the 4 examples above will give you wrong results over time (try adding 1734618, '2017-05-09 14:01:04'). This method is more efficient:



SELECT
  TOP 1 WITH TIES 
    *
FROM <yourtable>
ORDER BY
  row_number()over
    (partition by dateadd(hour, datediff(hour, 0, Date_time),0) ORDER BY Date_time)
+1


source


try it



select *
from table t1 inner join 
(select min(Date_time) as minDate_time from table 
group by convert(date,Date_time),CONVERT(VARCHAR(2),  Date_time, 114)) t2 
on t.Date_time= t2.minDate_time
0


source


This should do:

SELECT * 
FROM table 
WHERE table.Date_time IN 
( SELECT MIN(table.Date_time) AS Date_time
  FROM table 
  GROUP BY cast(table.Date_time as DATE), DatePart(HOUR,table.Date_time)
)
0


source 


SELECT * 
FROM table
WHERE Date_time IN ( 

 SELECT MIN(Date_time) 
 FROM table
 GROUP BY CONVERT(VARCHAR(8), Date_time, 112), DATEPART(HOUR,Date_time)

)

It will work for you.

0


source 


;With cte(Val,Date_time)
AS
(
SELECT 1734618,'2017-06-09 14:01:04' Union all
SELECT 1734609,'2017-06-09 14:00:05' Union all
SELECT 1734601,'2017-06-09 13:59:04' Union all
SELECT 1734593,'2017-06-09 13:58:04' Union all
SELECT 1734127,'2017-06-09 13:02:04' Union all
SELECT 1734119,'2017-06-09 13:01:04' Union all
SELECT 1734111,'2017-06-09 13:00:05' Union all
SELECT 1734103,'2017-06-09 12:59:04' 
)
SELECT Val,Date_time2 As Date_time FROM
(
SELECT Val,CASE WHEN DATEDIFF(HOUR,LagDate_time,Date_time)=1 Then Date_time ELSE NUll END AS Date_time2
From
(
SELECT *,Lag(Date_time, 1) OVER (ORDER BY Date_time,Val DESC) AS LagDate_time
FROM cte )
 Dt ) 
 )Dt2
WHERE Dt2.Date_time2 IS NOT NULL
ORDER BY Dt2.Date_time2 DESC

Output

Val     Date_time
1734609 2017-06-09 14:00:05
1734111 2017-06-09 13:00:05
0


source






More articles:


All Articles