sql - Returning unique results in a joined select -

i need query check msdb-database sql server agent job results. query follows:

 select convert(varchar(30), serverproperty('servername')),        a.run_status,        b.run_requested_date,        c.name,        case c.enabled          when 1 '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   (msdb.dbo.sysjobhistory         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  c.name   

so far good, running returns several results same jobs depending on how many times have ran until query. no good. want 1 result per job, , latest.

if add the string: b.session_id=(select max(session_id) msdb.dbo.sysjobactivity) works better, lists latest jobs depending on session_id parameter. exclude jobs haven't run while , not either.

can me this? have tried distinct and/or group can't work.

with cte      (select      convert(varchar(30), serverproperty('servername')) servername,      a.run_status,      b.run_requested_date,      c.name,      case c.enabled        when 1 'enabled'        else 'disabled'      end         enabled,      convert(varchar(10), convert(datetime, rtrim(19000101))+(a.run_duration         * 9 +      a.run_duration % 10000 * 6 + a.run_duration % 100 * 10) / 216e4, 108)         run_duration,      b.next_scheduled_run_date,      row_number() on (partition b.job_id order b.run_requested_date         desc) rn            (msdb.dbo.sysjobhistory                  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 *   cte  rn = 1 order  name