Question 254530 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – 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.

Tags:

2

Answers


  1. 0

    Get the latest value from repeating values

    Yes, if you would like to get the latest job_id for all rep_pms and also the remaining columns present in your table, you can try to use this SQL query:

    SELECT rep_pm, job_id, done
FROM 
(
  SELECT rep_pm, job_id, done,
         ROW_NUMBER() OVER (PARTITION BY rep_pm ORDER BY job_id DESC) 
    AS seq

  FROM Report_Table
) 
AS t
WHERE seq = 1;
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search