Is there a way to group rankings in SQL Teradata and then order them by which one has the most recent transaction?

Question

I am trying to get the ranking or grouping to count like in the custom_ranking_2 column:

I want it to count the rank like in the row custom_ranking_2, but everything I keep trying is counting it in the custom_ranking row.

My original query was:

,row_number() OVER (partition by custID, propID  ORDER BY trans_type desc, record_date desc) AS RANKING

In that query, I was trying to group by custID and propID and then order each by trans_type desc (which is r is 1, p is 2, o is 3, h is 4) and record_date desc.

I then needed it to group the ranking so I asked the question and I was told I needed the dense_rank function:

,dense_rank() OVER (partition by custID ORDER BY propID) AS custom_ranking

So now I am trying to solve how to keep the grouped ranking but still have the ranking done by propID with the most recent transaction on it.

Answer 1

You can use a subquery to get the maximum date:

select t.*,
       dense_rank() over (partition by custid order by max_record_date desc, propid)
from (select t.*,
             max(record_date) over (partition by custid, propid) as max_record_date
      from t
     ) t;

Or, if you prefer, join also works:

select t.*, tt.seqnum
from t join
     (select custid, propid,
             row_number() over (partition by custid order by max(record_date) desc) as seqnum
      from t
      group by custid, propid
     ) tt
     on tt.custid = t.custid and tt.propid = t.propid