Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

return top 5 per datepart Expand / Collapse
Author
Message
Posted Friday, July 12, 2013 2:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:47 AM
Points: 25, Visits: 108
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?
Post #1473227
Posted Friday, July 12, 2013 2:21 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 8, 2014 4:15 PM
Points: 727, Visits: 1,417
gchappell (7/12/2013)
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?


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
Post #1473229
Posted Friday, July 12, 2013 2:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:47 AM
Points: 25, Visits: 108
Thanks Fraggle i'm going to try it now
Post #1473234
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse