How to select top rows from a table when grouping data by hours?

Question

I have a table in SQL Server Management that contains error counts by hours. The table uses has four main columns Errors, StartTime, EndTime and Count. Is there a way I can select the top 1 error or top 5 errors that occur every hour?

A typical entry would look something like this:

Error	StartTime	EndTime	Count
error 1	2022-06-27 00:00:00	2022-06-27 01:00:00	8
error 2	2022-06-27 00:00:00	2022-06-27 01:00:00	9
error 1	2022-06-27 01:00:00	2022-06-27 02:00:00	1
error 3	2022-06-27 01:00:00	2022-06-27 02:00:00	19

For example, in this case, error 2 would be the top error between hours 0 and 1, and error 3 would be the top error between hours 1 and 2.

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Thom A, Jun 27 '22 at 14:22

score 1 · Answer 1 · answered Jun 27 '22 at 14:32

1

I have written a solution for you. Please check it , i hope it solves your problem

;WITH cte AS
(
SELECT *,
     dense_rank() OVER (PARTITION BY StartTime,EndTime ORDER BY Count DESC) AS 
rn
 FROM Table
  )
  SELECT *
  FROM cte
  WHERE rn = 1

answered Jun 27 '22 at 14:32

TouseefAwan

56
6

what if te timestamp don't are identical? – nbk Jun 27 '22 at 14:38

Kevin · Accepted Answer · 2022-06-27T15:14:10.580

1

WITH cte
AS (
    SELECT EndTime,
        Error,
        [Count],
        ROW_NUMBER() OVER (
            PARTITION BY EndTime ORDER BY [Count] DESC
            ) AS rn
    FROM TABLENAME
    )
SELECT EndTime,
    Error,
    [Count],
    rn
FROM cte
WHERE rn <= 5
ORDER BY EndTime,
    [Count] DESC

You can try here with DBFiddle

edited Jun 27 '22 at 15:14

answered Jun 27 '22 at 14:40

Kevin

7,162
11
46
70

1

You're right about the query not working. I used a table I already had but screwed up the script when changing names around. As for the rn <=5, he asked for top 1 or top 5, so I went with top 5 – Kevin Jun 27 '22 at 15:13
haha so they did. :P Without the group by this is a +1 from me. – Sean Lange Jun 27 '22 at 15:14
@SeanLange Fixed it. And added a dbfiddle link to boot – Kevin Jun 27 '22 at 15:15
@Kevin I tested the query against my database and it work as required! Thanks – Yash Jain Jun 27 '22 at 18:34

How to select top rows from a table when grouping data by hours?

2 Answers2