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
  • 1
    See https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql?rq=1 for how to find the row with `MAX(score)` in each group. Do this for test 1. Then join that with the table to get the test 2 score for the same student and week. – Barmar Jul 27 '22 at 23:04
  • 2
    You have this tagged both `mysql` and `sqlite`? Which are you actually using? – Barmar Jul 27 '22 at 23:05
  • @Barmar thank you but I still have not had success with that. I removed sqlite – bvowe Jul 27 '22 at 23:11
  • 1
    Please show what you tried, I'm not going to do your homework for you. – Barmar Jul 27 '22 at 23:12
  • @barmar SELECT WEEK, STUDENT, CLASS, MIN(SCORE) FROM DATABASE WHERE TEST == 1; – bvowe Jul 28 '22 at 00:36
  • I gave you a link to a question that shows how to get the row with the highest score in a group. That doesn't look anything like the solutions there, so I think you haven't really tried to solve this. – Barmar Jul 28 '22 at 00:38
  • @barmar i tried that and it didn't work. my attempt is based on manuals from sql – bvowe Jul 28 '22 at 00:39
  • You don't even have `GROUP BY WEEK, STUDENT`. Are you even trying? – Barmar Jul 28 '22 at 00:39
  • @Barmar i started using sql today because we are downsizing in our school district and they need the queries and i am the only one with rudimentary coding experience. i am sorry – bvowe Jul 28 '22 at 00:39
  • 1
    This is professional work, not homework? And they're expecting someone with no SQL experience to do it? – Barmar Jul 28 '22 at 00:40
  • @barmar yes welcome to the public school system! =) – bvowe Jul 28 '22 at 00:41

1 Answers1

1

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

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • i think it works! they should hire you ;) i really thank you for your time – bvowe Jul 28 '22 at 00:49
  • when I try your approach I get much more rows than I expect. The number of rows should be equals to Unique(STUDENT) * Unique(WEEK) – bvowe Jul 29 '22 at 21:05
  • 1
    I forgot to include the `score` in the first join, I've updated it. – Barmar Jul 29 '22 at 21:14
  • thank you this is so great. and can i ask one more thing? in the case for STUDENT 1 in WEEK 1 there score is a duplicate how do i just take their first one that appears and the corresponding test2 score? i try to add 'SELECT DISTINCT' but that does not work because the TEST2 values are differ – bvowe Jul 29 '22 at 21:17
  • Unless you can use window functions (MySQL 8.x), you'll need another level of nested subqueries to pick just one row from each group. – Barmar Jul 29 '22 at 21:18
  • they use ORACLE – bvowe Jul 29 '22 at 21:19
  • Why did you tag the question `mysql` if you're using Oracle? – Barmar Jul 29 '22 at 21:21
  • Window functions were added in Oracle 8i. Read the documentation to learn how to do it. – Barmar Jul 29 '22 at 21:22