CODEWITHSUNDEEP
mysqlsql
bvowe
bvowe

Reputation: 3384

SQL Select Max BY Group

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.

Upvotes: 0

Views: 88

Answers (1)

Barmar
Barmar

Reputation: 780724

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 AND t1.score = t2.score
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
ORDER BY student, week

DEMO

Upvotes: 1

Related Questions