By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE YourTable (
"id" INTEGER,
"timestamp" datetime,
"value" INTEGER
);
INSERT INTO YourTable
VALUES
('1', '2015-01-01 00:00:00', '128'),
('2', '2015-01-01 00:00:01', '128'),
('3', '2015-01-01 00:00:04', '129'),
('4', '2015-01-01 00:00:05', '123'),
('67', '2015-01-01 00:00:59', '128');
5 rows affected
SELECT
t.StartTime,
EndTime = DATEADD(second, 59, t.StartTime),
number = COUNT(*)
FROM (
SELECT *,
rn = DENSE_RANK() OVER (PARTITION BY v.StartTime ORDER BY t.value DESC)
FROM YourTable t
CROSS APPLY (VALUES (
DATEADD(minute, DATEDIFF(minute, '20000101', t.timestamp), '20000101')
)) v(StartTime)
) t
WHERE rn <= 2
GROUP BY
t.StartTime;
| StartTime | EndTime | number |
|---|---|---|
| 2015-01-01 00:00:00.000 | 2015-01-01 00:00:59.000 | 4 |