SQL Select Max BY Group-CodePudding

I have this:

WEEK    STUDENT CLASS   TEST    SCORE
1   1   A   1   93
1   1   A   2   97
1   1   B   1   72
1   1   B   2   68
1   1   C   1   93
1   1   C   2   51
1   1   H   1   19
1   2   A   1   88
1   2   A   2   56
1   2   B   1   53
1   2   B   2   79
1   2   C   1   69
1   2   C   2   90
1   2   H   1   61
1   3   A   1   74
1   3   A   2   50
1   3   B   1   76
1   3   B   2   97
1   3   C   1   55
1   3   C   2   63
1   3   H   1   63
2   1   A   1   59
2   1   A   2   68
2   1   B   1   77
2   1   B   2   80
2   1   C   1   52
2   1   C   2   94
2   1   H   1   74
2   2   A   1   64
2   2   A   2   74
2   2   B   1   92
2   2   B   2   98
2   2   C   1   89
2   2   C   2   84
2   2   H   1   54
2   3   A   1   51
2   3   A   2   82
2   3   B   1   86
2   3   B   2   51
2   3   C   1   90
2   3   C   2   72
2   3   H   1   86

and want this:

WEEK    STUDENT CLASS   H   TEST1   TEST2
1   1   A   19  93  97
1   2   A   61  88  56
1   3   B   63  76  97
2   1   B   74  77  80
2   2   B   54  92  98
2   3   C   86  90  72

I wish to group by WEEK and STUDENT and then for each combination of WEEK and STUDENT find the CLASS when SCORE equals to maximum(SCORE) where TEST equals to one. Then I wish to find the corresponding SCORE for TEST equals to 2 using that same CLASS. I wish to transform this into the data WANT from the data HAVE. And ALSO add the COLUMN H where it is just equals to the SCORE when CLASS equals to H. For ties you can just take the first one

CodePudding user response：

Write a subquery that gets the highest score for each week and student on test 1. Join that with the table to get the rest of the row for that same score.

Then join that with the table again to get the row for the same student, week, and class, but with test = 2.

SELECT t1.week, t1.student, t1.class, t1.score AS test1, t3.score AS test2
FROM yourTable AS t1
JOIN (
    SELECT week, student, MAX(score) AS score
    FROM yourTable
    WHERE test = 1
    GROUP BY week, student
) AS t2 ON t1.week = t2.week AND t1.student = t2.student
JOIN yourTable AS t3 ON t3.week = t1.week AND t3.student = t1.student AND t3.class = t1.class
WHERE t1.test = 1 AND t3.test = 2