1

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

I have this table:

enter image description here

I am trying to get to this table:

enter image description here

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

2

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

  • Yes I forgot one VERY important detail a column that show the time that id tried (number of sequences) – user22478 Dec 12 '18 at 19:41
  • It is definitely my fault of not explaining- I apologize - cnt is the count of the number of presence of A for each ID. – user22478 Dec 12 '18 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.