Select the Max row number for an account

Question

I need to only pull the max row number for an account. I know it's a grouping issue.

Current data:

ACCOUNT_UID	ID	NAME	ACADEMIC_PERIOD	CAT_BY_DATE	CAT_DATE	MAX_ROW
abc	abc	Popeye	202190	CPT	9/15/2021	1
abc	abc	Popeye	202190	CSH	10/4/2021	2

I only need the second row.

Current query:

Select
A.ACCOUNT_UID,
A.ID,
A.NAME,
A.ACADEMIC_PERIOD,
A.cat_by_date,
A.Cat_date,
Max (A.row_num) max_row

From
(select RAD.ACCOUNT_UID,
RAD.ID,
RAD.NAME,
RAD.ACADEMIC_PERIOD,
listagg(RAD.CATEGORY, ', ') within group (order by RAD.CATEGORY) as cat_by_date,
trunc(RAD.TRANSACTION_DATE) as Cat_date,
ROW_NUMBER() OVER (partition by RAD.ACCOUNT_UID ORDER BY trunc(RAD.TRANSACTION_DATE))  as 
row_num

from RAD
where RAD.ACADEMIC_PERIOD ='202190'
and RAD.CATEGORY in ('CPT', 'CSH')

group by
RAD.ACCOUNT_UID,
RAD.ID,
RAD.NAME,
RAD.ACADEMIC_PERIOD,
trunc(RAD.TRANSACTION_DATE)
order by 1 ) A

group by
A.ACCOUNT_UID,
A.ID,
A.NAME,
A.ACADEMIC_PERIOD
A.cat_by_date,
A.Cat_date

Order by 1

Answer 1

You can try the following:

Select
  A.ACCOUNT_UID,
  A.ID,
  A.NAME,
  A.ACADEMIC_PERIOD,
  A.cat_by_date,
  A.Cat_date
From
  (select RAD.ACCOUNT_UID,
          RAD.ID,
          RAD.NAME,
          RAD.ACADEMIC_PERIOD,
          listagg(RAD.CATEGORY, ', ') within group (order by RAD.CATEGORY) as cat_by_date,
          trunc(RAD.TRANSACTION_DATE) as Cat_date,
          ROW_NUMBER() OVER (partition by RAD.ACCOUNT_UID ORDER BY trunc(RAD.TRANSACTION_DATE) DESC)  as rn
     from RAD
    where RAD.ACADEMIC_PERIOD ='202190'
      and RAD.CATEGORY in ('CPT', 'CSH')
    group by
          RAD.ACCOUNT_UID,
          RAD.ID,
          RAD.NAME,
          RAD.ACADEMIC_PERIOD,
          trunc(RAD.TRANSACTION_DATE)
    order by 1 ) A
Where A.rn = 1
Order by 1

The idea is to apply the row_number window function in descending order of transaction_date per every account_uid and pick the first row obtained.