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 │ 23 │ 2019-10-04 11:42:00 ║
╟────┼───────┼───────┼─────────────────────╢
║ 7 │ james │ 86 │ 2019-10-05 12:11:20 ║
╚════╧═══════╧═══════╧═════════════════════╝
First I want to select all the names from the table BUT I only want their most recent record. 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.
So to do that I get:
╔════╤═══════╤═══════╤═════════════════════╗
║ id │ name │ grade │ date_added ║
╠════╪═══════╪═══════╪═════════════════════╣
║ 5 │ mike │ 83 │ 2019-10-04 09:28:43 ║
╟────┼───────┼───────┼─────────────────────╢
║ 6 │ bob │ 23 │ 2019-10-04 11:42:00 ║
╟────┼───────┼───────┼─────────────────────╢
║ 7 │ james │ 86 │ 2019-10-05 12:11:20 ║
╚════╧═══════╧═══════╧═════════════════════╝
.
SELECT *
FROM student_grade
GROUP BY id
ORDER BY id DESC
Now I want to randomize those rows and get the first 2 rows of those randomized rows
╔════╤═══════╤═══════╤═════════════════════╗
║ id │ name │ grade │ date_added ║
╠════╪═══════╪═══════╪═════════════════════╣
║ 7 │ james │ 86 │ 2019-10-05 12:11:20 ║
╟────┼───────┼───────┼─────────────────────╢
║ 5 │ mike │ 83 │ 2019-10-04 09:28:43 ║
╚════╧═══════╧═══════╧═════════════════════╝
How do I randomize those 3 rows. My end goal is to get the latest records of each student. I don't care about their past records. I just want their most recent ones, and then I want to randomize them. What is the most efficient way of me doing this?
Aucun commentaire:
Enregistrer un commentaire