How to get the last record for unique people ids

Question

I have students that take the ACT test multiple times a year, but I only want the last ACT score they have taken.

My table has StudentID, ACTDate (YYYYMM format only), and ACTScore.

How can I get the last/latest score for every student?

I've looked at Unique, First, Limit... - all do not do what I want! I'm a very new to SQL (but not programming), please be gentle! :)

I'm using SQuirrel SQL 3.7 - MySQL if that matters!

given that ids are assigned in an incremental manner, you coud just select the max id from actscore and that should give you the last row inserted in that table. If you want to be more formal, store the date of the score in your table. — Prefijo Sustantivo, Apr 11 '16 at 20:27
http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html — JamieD77, Apr 11 '16 at 20:29

score 0 · Answer 1 · answered Apr 11 '16 at 20:31

0

select a.StudentID, a.ACTScore, a.ACTDate
from mytable a
where a.ACTDate = (select max(b.ACTDate)
                     from mytable b
                    where a.StudentID = b.StudentID);

answered Apr 11 '16 at 20:31

T Gray

712
4
10

score 0 · Answer 2 · edited May 23 '17 at 10:28

0

This should answer your question. You would just need to change it out for your ACT scores

edited May 23 '17 at 10:28

Community

1
1

answered Apr 11 '16 at 20:32

jumper

74
1
9

score 0 · Answer 3 · answered Apr 11 '16 at 20:40

In MSSQL or Oracle I would do:

    SELECT 
        StudentID, 
        MAX(ACTDate) AS LAST_DATE, 
        MAX(ACTScore) AS Score
    FROM Students
    GROUP BY StudentID

So we just group the students by their ID and we use an aggregate function MAX over the date that will show us the latest date, then we use another aggregate function MAX over the ACTScore in order for us to include it in the selection.

I guess that something similar should be done in MySQL. Let me know if it (not)worked.

How to get the last record for unique people ids

3 Answers3