clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 582759 fiddles created (13358 in the last week).

CREATE TABLE T ( ID INT, TYPE CHAR(1), SEQ INT, GRADE CHAR(1));
 hidden batch(es)


INSERT INTO T VALUES (1, 'A', 1, 'I');
1 rows affected
 hidden batch(es)


INSERT INTO T VALUES (1, 'A', 2, 'W');
1 rows affected
 hidden batch(es)


INSERT INTO T VALUES (1, 'A', 3, 'W');
1 rows affected
 hidden batch(es)


INSERT INTO T VALUES (1, 'A', 4, 'C');
1 rows affected
 hidden batch(es)


INSERT INTO T VALUES (2, 'B', 1, 'W');
1 rows affected
 hidden batch(es)


INSERT INTO T VALUES (2, 'B', 2, 'A');
1 rows affected
 hidden batch(es)


INSERT INTO T VALUES (3, 'A', 1, 'I');
1 rows affected
 hidden batch(es)


INSERT INTO T VALUES (3, 'A', 2, 'W');
1 rows affected
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)