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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityAs 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.
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