Top x records per group
-
Hi everybody!
Maybe this question has already been post but I haven't been able to find it in this site. My specific problem is this: I need a query to obtain information about the last X (let's say 5) executions of every job within the server.
For those that don't know, the information is stored in the msdb.dbo.SysJobHistory table. I found a similar issue in http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=210231 and Ninja's_RGR'us gave a solution. The problem is that in SysJobHistory the primary key is composite (job_id, step_id, run_date, run_time) and not a single field like SysObjects.
Any idea? Thanks in advance
-
I was wrong, the primary key of the SysJobHistory table is instance_id, thus I can use Ninja's_RGR'us' solution.
-
NO!
For SQL Server 2005 there are more efficient methods. Read about the ROW_NUMBER() function.
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... 
AS RecID FROM SysJobHistory) AS x WHERE RecID BETWEEN 1 AND 5
N 56°04'39.16"
E 12°55'05.25" -
You're right Peter! Thank you.
SELECT JH.instance_id, J.name AS JobName, JH.run_date, JH.run_time, JH.run_status
FROM dbo.SysJobHistory AS JH
INNER JOIN dbo.SysJobs AS J ON JH.Job_Id = J.Job_Id
WHERE EXISTS ( SELECT 1
FROM (SELECT instance_id, run_date
, ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY Run_Date DESC) AS ExecutionNumber
FROM dbo.SysJobHistory
WHERE step_id = 0
) AS A
WHERE JH.Instance_Id = A.Instance_Id AND A.ExecutionNumber <=5
)
ORDER BY J.name, JH.run_date DESC, JH.run_time DESC
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply