Is there a way to select records inserted in previous hour from the last recorded inserted record?-CodePudding

I have a table that looks like this that has quite a few records in it:


 --------- ------ ------------------------  
|   unit  | temp |     login_time_utc     |
 --------- ------ ------------------------ 
|    1    |  53  |   2022-01-24 10:02:06  |
|    1    |  62  |   2022-01-24 10:10:01  |
|    2    |  34  |   2022-01-24 10:04:00  |
|    2    |  65  |   2022-01-24 16:08:59  |
|    2    |  65  |   2022-01-24 16:03:56  |
|    2    |  74  |   2022-01-24 16:06:53  |
|    3    |  74  |   2022-01-24 16:05:51  |
|    3    |  83  |   2022-01-24 17:09:49  |
|    3    |  73  |   2022-01-24 18:07:46  |
|    4    |  74  |   2022-01-24 18:11:43  |
 --------- ------ ------------------------

I would like to select all the records for each unit that were inserted in the last hour from the most recently inserted record of that respective unit. Is that possible?

I can do this easily if its just the last hour from now, but I don't know how to do this if its the last hour of each units most recent insert.

I cannot use a loop or a cursor in this situation.

CodePudding user response：

with cutoff as (
    select unit, max(login_time_utc) as max_login
    from T group by unit
)
select data.*
from cutoff cross apply (
    select * from T t
    where t.unit = cutoff.unit
      and t.login_time_utc >= dateadd(hour, -1, cutoff.max_login)
) as data

CodePudding user response：

You can use a window function and a CTE to identify the MAX date per unit. Then use DATEDIFF to find all the records in the last hour.

WITH cte AS (
    SELECT *, MAX(login_time_utc) OVER (PARTITION BY unit) AS login_time_utc_max
    FROM yourtable
)
SELECT unit, temp, login_time_utc
FROM cte
WHERE DATEDIFF(SS, login_time_utc, login_time_utc_max) <= 3600
ORDER BY login_time_utc