Get last ID of repeated values based on type and most recent sequence

Question

As I am learning sql I am stuck on this one:

I have this table:

I am trying to get to this table:

The criteria is pick any ID that has Type A and its grade based on the most recent seq.

Answer 1

In this case you should generate a sub-query with the aggregated values:

SELECT 
    ID,
    TYPE,
    MAX(SEQ) SEQ,
    COUNT(*) CNT
FROM
    T 
GROUP BY
    ID,
    TYPE;

ID | TYPE | SEQ | CNT
-: | :--- | --: | --:
 3 | A    |   2 |   2
 1 | A    |   4 |   4
 2 | B    |   2 |   2

And then join it with your table ON SEQ = MAX(SEQ):

SELECT 
    T1.ID,
    T1.TYPE,
    T1.SEQ,
    T1.GRADE,
    T2.CNT
FROM 
    T T1
JOIN
    (SELECT 
         ID,
         TYPE,
         MAX(SEQ) MAX_SEQ,
         COUNT(*) CNT
     FROM
         T
     GROUP BY
         ID,
         TYPE) T2
     ON T2.ID = T1.ID
     AND T2.TYPE = T1.TYPE
     AND T1.SEQ = T2.MAX_SEQ
WHERE
    T1.TYPE = 'A';

ID | TYPE | SEQ | GRADE | CNT
-: | :--- | --: | :---- | --:
 3 | A    |   2 | W     |   2
 1 | A    |   4 | C     |   4

db<>fiddle here