how to get the last record when two columns have same values?

Question

i want to get a last row based of Updated_ts on the bases of same gr_number and course

i am using below query.

select t.*
from (select  st.gr_number,st.course,st.is_active_flg,st.status,st.updated_ts

       ,sum(case when st.status = 'COMPLETED' then 1 else 0 end) over (partition by st.gr_number) as completedCourse,
      sum(case when st.status <> 'COMPLETED' and st.status <> 'APPLICATION' and st.is_active_flg = 'N' then 1 else 0 end) over (partition by st.gr_number) as IncompletCourse
      from admission_log st     
      join course cr on cr.course_id=st.course
      order by st.gr_number,st.course,st.updated_ts
     ) t
where completedCourse > 0 and IncompletCourse > 0;

this query gives me result as

from above result i want only last value against the same gr_number and course based of Updated_ts

like

please help

Answer 1

I think you can use row_number():

select t.*
from (select st.gr_number, st.course, st.is_active_flg, st.status, st.updated_ts,
            sum(case when st.status = 'COMPLETED'
                     then 1 else 0 
                end) over (partition by st.gr_number) as completedCourse,
            sum(case when st.status <> 'COMPLETED' and st.status <> 'APPLICATION' and st.is_active_flg = 'N'
                     then 1 else 0
                end) over (partition by st.gr_number) as IncompletCourse,
            row_number() over (partition by st.gr_number order by updated_ts desc) as seqnum
      from admission_log st join    
           course cr
           on cr.course_id = st.course
     ) t
where completedCourse > 0 and IncompletCourse > 0 and
      seqnum = 1;

I don't think your where filters are needed, but I have left them in.