Return unique results to a combined selection

Question

I need help with a query that checks the MSDB database for SQL Server Agent results. My request is as follows:

SELECT CONVERT(VARCHAR(30), Serverproperty('ServerName')),
       a.run_status,
       b.run_requested_date,
       c.name,
       CASE c.enabled
         WHEN 1 THEN 'Enabled'
         ELSE 'Disabled'
       END,
       CONVERT(VARCHAR(10), CONVERT(DATETIME, Rtrim(19000101))+(a.run_duration *
       9 +
       a.run_duration % 10000 * 6 + a.run_duration % 100 * 10) / 216e4, 108),
       b.next_scheduled_run_date
FROM   (msdb.dbo.sysjobhistory a
        LEFT JOIN msdb.dbo.sysjobactivity b
          ON b.job_history_id = a.instance_id)
       JOIN msdb.dbo.sysjobs c
         ON b.job_id = c.job_id
ORDER  BY c.name  

While everything is fine, but it works, it returns several results for the same tasks, depending on how many times they were run before the request. This is not good. I want only one result for each work and only the last.

If I add the line: WHERE b.session_id = (SELECT MAX (session_id) from msdb.dbo.sysjobactivity) It works better, but then it only displays the latest jobs depending on the session_id parameter. This eliminates tasks that have not been completed for a while, and are also not very good.

- ? DISTINCT / GROUP BY, .

Answer 1

with cte
     AS (SELECT
     Convert(varchar(30), SERVERPROPERTY('ServerName')) AS ServerName,
     a.run_status,
     b.run_requested_date,
     c.name,
     CASE c.enabled
       WHEN 1 THEN 'Enabled'
       Else 'Disabled'
     END
        AS Enabled,
     CONVERT(VARCHAR(10), CONVERT(DATETIME, RTRIM(19000101))+(a.run_duration
        * 9 +
     a.run_duration % 10000 * 6 + a.run_duration % 100 * 10) / 216e4, 108)
        AS run_duration,
     b.next_scheduled_run_date,
     ROW_NUMBER() over (partition by b.job_id ORDER BY b.run_requested_date
        DESC) AS RN
         FROM   (msdb.dbo.sysjobhistory a
                 LEFT JOIN msdb.dbo.sysjobactivity b
                   ON b.job_history_id = a.instance_id)
                join msdb.dbo.sysjobs c
                  on b.job_id = c.job_id)
SELECT *
FROM   cte
WHERE  RN = 1
ORDER  BY name