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

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.

CodePudding user response：

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

CodePudding user response：

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