July 12, 2013 at 2:02 pm
i am wanting to return the top 5 errors by hour. the following query returns all
select count(ERROR_LOG_NO) as ErrorCount, error_name, DatePart(Hour,ERROR_LOG_DATE) as ErrorHour, ERROR_SEVERITY
from my_table
where error_severity < 4
group by DatePart(Hour,ERROR_LOG_DATE), error_name, Error_Severity
order by ErrorHour desc, ErrorCount desc
the following returns the top 5 form the latest hour
select top(5)count(ERROR_LOG_NO) as ErrorCount, error_name, DatePart(Hour,ERROR_LOG_DATE) as ErrorHour, ERROR_SEVERITY
from my_table
where error_severity < 4
group by DatePart(Hour,ERROR_LOG_DATE), error_name, Error_Severity
order by ErrorHour desc, ErrorCount desc
how do I combine the two queries to return the top5 for each datepart?
July 12, 2013 at 2:21 pm
gchappell (7/12/2013)
i am wanting to return the top 5 errors by hour. the following query returns allselect count(ERROR_LOG_NO) as ErrorCount, error_name, DatePart(Hour,ERROR_LOG_DATE) as ErrorHour, ERROR_SEVERITY
from my_table
where error_severity < 4
group by DatePart(Hour,ERROR_LOG_DATE), error_name, Error_Severity
order by ErrorHour desc, ErrorCount desc
the following returns the top 5 form the latest hour
select top(5)count(ERROR_LOG_NO) as ErrorCount, error_name, DatePart(Hour,ERROR_LOG_DATE) as ErrorHour, ERROR_SEVERITY
from my_table
where error_severity < 4
group by DatePart(Hour,ERROR_LOG_DATE), error_name, Error_Severity
order by ErrorHour desc, ErrorCount desc
how do I combine the two queries to return the top5 for each datepart?
Your going to want to do a subquery using the row_number() over (partition by DATEPART() ORDER BY ErrorHour desc, errorcount desc). This will provide you with the numbers. Then in the outer query, just state where the row_number column <= 5
so something like this:
SELECT *
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY DATEPART(HOUR, ERROR_LOG_DATE) ORDER BY DATEPART(HOUR, Error_log_Date)) AS ID
, Count(Error_log_No) OVER (Partition by DATEPART(HOUR, Error_log_date)) ErrorCount
, Error_Severity
from table
where error_severity < 4
)
WHERE ID <= 5
Performance note, if you expect this table to grow, I would suggest adding a new column that stores the DatePart so you aren't having to use the DATEPART function each query, which would kill performance on large tables.
Fraggle
July 12, 2013 at 2:44 pm
Thanks Fraggle i'm going to try it now
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply