By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 17763: ) (Hypervisor) |
CREATE TABLE Example (
Name VARCHAR(512),
TimeStamp datetime
);
INSERT INTO Example(Name, TimeStamp) VALUES
('Bob', '20240505 15:00'),
('Ted', '20240505 15:06'),
('Alice', '20240505 15:07'),
('John', '20240505 15:08'),
('Denver', '20240505 15:11');
SELECT * FROM Example;
| Name | TimeStamp |
|---|---|
| Bob | 2024-05-05 15:00:00.000 |
| Ted | 2024-05-05 15:06:00.000 |
| Alice | 2024-05-05 15:07:00.000 |
| John | 2024-05-05 15:08:00.000 |
| Denver | 2024-05-05 15:11:00.000 |
select
name, timestamp
from (
select
*,
row_number() over(
partition by datediff(minute, 0, timestamp)/5*5
order by timestamp desc
) as rn
from example
) as t0
where rn = 1;
| name | timestamp |
|---|---|
| Bob | 2024-05-05 15:00:00.000 |
| John | 2024-05-05 15:08:00.000 |
| Denver | 2024-05-05 15:11:00.000 |