0

I am trying to select the latest record of all the students (I don't want a student's past grade, only their most recent). Currently, its just returning me one result.

student_grade table

╔════╤═══════╤═══════╤═════════════════════╗
║ 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 ║
╚════╧═══════╧═══════╧═════════════════════╝

What I want it to return

╔════╤═══════╤═══════╤═════════════════════╗
║ 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 ║
╚════╧═══════╧═══════╧═════════════════════╝

My sql statement

SELECT id, DISTINCT name, grade, max(date_added)
FROM student_grade
ORDER BY date_added DESC

Or an efficient way of returning me this detail. I'm a little stuck as to how I can get this.

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • This is a very common problem. I have added [tag:greatest-n-per-group] tag to the question. You can get a plethora of techniques here: https://stackoverflow.com/questions/tagged/greatest-n-per-group?tab=Votes – Madhur Bhaiya Oct 05 '19 at 12:48
  • use this SELECT s_g.id,s_g.name,s_g.grade,s_g.date_added FROM student_grade s_g INNER JOIN ( SELECT name, max(grade) as maxgrade FROM student_grade GROUP BY name ) s_g1 ON s_g1.name = s_g.name and s_g1.maxgrade = s_g.grade – nbk Oct 05 '19 at 13:03
  • @nbk with this you are rertrieving the row(s) matching the highest grade of each student instead of the latest grade. Thus, for the ```bob``` record you will get id 4 instead of 6 – Olivier Depriester Oct 05 '19 at 13:08
  • i know but id seems only to be a autoincrement value without any other purpose, so i don't think that he needs the biggest id instead the highest grade, but this hould be asked before hand – nbk Oct 05 '19 at 13:12

1 Answers1

0

Use a subquery to first select the max id for each name and then the record related to this id :

SELECT sg.*
FROM student_grade sg
     INNER JOIN (
          SELECT name, max(id) as id
          FROM student_grade
          GROUP BY name
    ) x ON x.name = sg.name AND x.id = sg.id

I assumed that the id is the right field to find the last added record. Maybe date_added would be more rightful but if they have the same functional meaning, using id is more efficient

Olivier Depriester
  • 1,615
  • 1
  • 7
  • 20