Get the latest value from repeating values

I have a table named Report_Table in Azure SQL. It has more than one hundred columns.

I will give an example with 3 columns like:

+--------+----------+------+
| job_id | rep_pm   | done |
+--------+----------+------+
|  46    | a002     |    a |
|  47    | a002     |    b |
|  48    | a002     |    c |
|  49    | a003     |    d |
|  50    | a003     |    e |
|  51    | a003     |    f |
|  52    | a004     |    g |
|  53    | a004     |    h |
|  54    | a005     |    i |
|  55    | a006     |    j |
+--------+----------+------+

Column rep_pm have repeating values. I need to fetch the latest value of rep_pm.

It will depend upon column job_id.

• For a002 latest jod_id will be 48
• For a003 latest jod_id will be 51
• For a004 latest jod_id will be 53

and so on.

I need the latest job_id‘s for all the rep_pm‘s and also the remaining columns present in the table.

This is what I tried:

select 
    rep_pm, count(*) as cnt,
    row_number() over (partition by rep_pm order by count(*) desc) as seq
from 
    Report_Table 
group by 
    rep_pm

Above query is returning this output:

+--------+----------+------+
| rep_pm | cnt      | seq  |
+--------+----------+------+
|  a002  | 3        |    1 |
|  a003  | 3        |    1 |
|  a004  | 2        |    1 |
|  a005  | 1        |    1 |
|  a005  | 1        |    1 |
+--------+----------+------+

I know I am missing something important, would appreciate your help.

>Solution :

you can try :-

 select rt.*  
 from Report_Table rt
 join (select rep_pm,max(job_id) as job_id from Report_Table group by rep_pm)x
 on rt.job_id=x.job_id and rt.rep_pm=x.rep_pm