Select the latest rows and exactly 2 rows with one condition and 2 with a different condition

Question

I have a table called student_grades

╔════╤═══════╤═══════╤═════════════════════╗
║ id │ name  │ grade │ date_added          ║
╠════╪═══════╪═══════╪═════════════════════╣
║ 1  │ bob   │ 23    │ 2019-10-01 14:25:00 ║
╟────┼───────┼───────┼─────────────────────╢
║ 2  │ james │ 45    │ 2019-10-02 17:31:27 ║
╟────┼───────┼───────┼─────────────────────╢
║ 3  │ mike  │ 42    │ 2019-10-03 18:08:13 ║
╟────┼───────┼───────┼─────────────────────╢
║ 4  │ bob   │ 68    │ 2019-10-04 02:00:00 ║
╟────┼───────┼───────┼─────────────────────╢
║ 5  │ mike  │ 83    │ 2019-10-04 09:28:43 ║
╟────┼───────┼───────┼─────────────────────╢
║ 6  │ bob   │ 93    │ 2019-10-04 11:42:00 ║
╟────┼───────┼───────┼─────────────────────╢
║ 7  │ james │ 98    │ 2019-10-05 14:51:20 ║
╟────┼───────┼───────┼─────────────────────╢
║ 8  │ steph │ 72    │ 2019-10-05 15:31:20 ║
╟────┼───────┼───────┼─────────────────────╢
║ 9  │ john  │ 78    │ 2019-10-05 16:33:20 ║
╟────┼───────┼───────┼─────────────────────╢
║ 10 │ john  │ 74    │ 2019-10-05 17:42:23 ║
╟────┼───────┼───────┼─────────────────────╢
║ 10 │ nick  │ 92    │ 2019-10-05 17:59:12 ║
╚════╧═══════╧═══════╧═════════════════════╝

I use this statement to get the LATEST records for a student. For example. James has 2 records. One with id 2 AND ONE WITH id 7. So I want the one with id 7 because the id is larger.

Then I randomize those rows and it returns me

╔════╤═══════╤═══════╤═════════════════════╗
║ id │ name  │ grade │ date_added          ║
╠════╪═══════╪═══════╪═════════════════════╣
║ 7  │ james │ 86    │ 2019-10-05 12:11:20 ║
╟────┼───────┼───────┼─────────────────────╢
║ 5  │ mike  │ 83    │ 2019-10-04 09:28:43 ║
╚════╧═══════╧═══════╧═════════════════════╝

Statement:

SELECT s1.*
FROM student_grade s1
JOIN (SELECT name, MAX(id) AS id
      FROM student_grade
      GROUP BY name) s2 ON s2.name = s1.name AND s2.id = s1.id
ORDER BY RAND()
LIMIT 2;

My question is, how can I select the latest records with 2 students who scored between 70 and 80 (selected randomly) and 2 students who scored between 90 and 99 (selected randomly).

SELECT s1.*
FROM student_grade s1
JOIN (SELECT name, MAX(id) AS id
      FROM student_grade
      WHERE (grade >= 70 and grade <= 80) or (grade >= 90 and grade <= 99)
      GROUP BY name) s2 ON s2.name = s1.name AND s2.id = s1.id
ORDER BY RAND()
LIMIT 4;

But if I do the statement above, it may give me 3 students who scored 70-80 and 1 student who scored 90-99. I want exactly 2 students from 70-80 (selected randomly) and exactly 2 (selected randomly) from 90-99. How can I do this?

Answer 1

You can UNION ALL together two of your existing query to get the results you want:

(SELECT s1.*
 FROM student_grade s1
 JOIN (SELECT name, MAX(id) AS id
       FROM student_grade
       WHERE grade BETWEEN 70 AND 80
       GROUP BY name) s2 ON s2.name = s1.name AND s2.id = s1.id
 ORDER BY RAND()
 LIMIT 2)
UNION ALL
(SELECT s1.*
 FROM student_grade s1
 JOIN (SELECT name, MAX(id) AS id
       FROM student_grade
       WHERE grade BETWEEN 90 AND 99
       GROUP BY name) s2 ON s2.name = s1.name AND s2.id = s1.id
 ORDER BY RAND()
 LIMIT 2)

Demo on dbfiddle

We can use UNION ALL since we know there will not be any duplicates because the grade ranges are different.