0
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

I wish to group by STUDENT and WEEK and find the MAXIMUM(SCORE) value when TEST = 1. Then I wish to add the corresponding rows for CLASS and also the score for TEST = 2 based to get this:

WEEK    STUDENT CLASS   TEST1   TEST2
1   1   A   93  97
2   1   A   88  56
1   2   B   76  97
2   2   B   77  80
1   3   B   92  98
2   3   C   90  72

This is what I try but in SQL I am no able to SELECT columns which I don't group by

SELECT STUDENT, WEEK, CLASS, MAX(SCORE)
FROM DATA
WHERE TEST = 1
GROUP BY (STUDENT, WEEK)

but I do not find a solution that works.

bvowe
  • 3,004
  • 3
  • 16
  • 33
  • In sql equals is `=` not `==` – Jens Jul 29 '22 at 20:58
  • You asked this before: https://stackoverflow.com/questions/73145461/sql-select-max-by-group/73146036#73146036 – Barmar Jul 29 '22 at 21:00
  • @Barmar yes, i thought your approach worked but when i do it i get way more rows than i should and am not sure how to remedy so i try to ask the question more clear. i am not a student in class – bvowe Jul 29 '22 at 21:00
  • If my answer isn't correct, you should comment on the answer and update the question. Don't post the same question again. – Barmar Jul 29 '22 at 21:01
  • @barmar ok i do that now i just thought this is more clearer – bvowe Jul 29 '22 at 21:03

0 Answers0