CODEWITHSUNDEEP
sqlsql-serverselect
Mr.Takeo
Mr.Takeo

Reputation: 223

SQL : How to query selecting only first row in every hour?

I try to find how to select a first row of every hour from my recording devices that record data every minute to database.

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 are I don't know how to query only first row from time data every hour and I don't know why I can't use DATE_FORMAT() function in Microsoft SQL server management there is no this function in autocomplete-text suggests at all. I found this one selecting every minute it doesn't work for me because of no DATE_FORMAT() function only. 

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 output : 2 rows

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 helps.

edited : With FORMAT() function it works.

Upvotes: 5

Views: 3929

Answers (5)

user7715598
user7715598

Reputation: 

;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

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44336

Do not use solutions that uses subselects, the performance will suffer and the current 4 examples above will over time give you an incorrect result (try adding 1734618, '2017-05-09 14:01:04'). This method is more effective:

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)

Upvotes: 9

Tien Nguyen Ngoc
Tien Nguyen Ngoc

Reputation: 1555

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.

Upvotes: 0

Prabhat G
Prabhat G

Reputation: 3029

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)
)

Upvotes: 0

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

Try this

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

Upvotes: 0

Related Questions