Select max value in subquery

Question

I have these two tables:

Student:

|                name |                   email |
|---------------------|-------------------------|
|    Arturo     Vidal |     [email protected] |
|   Bastian   Quezada |          [email protected] |
|    Javier     Jeria |           [email protected] |
| Sebastian    Piñera | [email protected] |
| Sebastian  Gallardo |        [email protected] |

Class:

| classId |                   email |  signUpDate |
|---------|-------------------------|-------------|
|       1 |        [email protected] |  2018-01-01 |
|       1 |           [email protected] |  2019-10-01 |
|       1 |          [email protected] |  2018-07-01 |
|       2 |        [email protected] |  2018-05-04 |
|       2 |          [email protected] |  2018-01-01 |
|       3 |          [email protected] |  2018-12-05 |
|       3 |        [email protected] |  2018-02-01 |
|       4 |     [email protected] |  2018-03-01 |
|       5 | [email protected] |  2018-03-01 |

I want to show the name the last student that signed up for each classId. That means, I should get a name for classId 1, one for classId 2, etc. My solution for firstly getting the mails (to know the student's name after) is this:

select classId, email, max(signUpDate)
from Class
group by classId

it prints the max date, which is ok, but it also prints the wrong mails for each date:

| ClassId |                   email | max(signUpDate) |
|---------|-------------------------|-----------------|
|       1 |        [email protected] |      2019-10-01 |
|       2 |        [email protected] |      2018-05-04 |
|       3 |          [email protected] |      2018-12-05 |
|       4 |     [email protected] |      2018-03-01 |
|       5 | [email protected] |      2018-03-01 |

which is completely wrong (). Therefore, when I try to join the the values for getting the names, I get incorrect values.

In other words, I don't understand why are the rows mixing up. Is there any solution for getting correct emails for the max(signUpDate) for each ClassId?

Thanks for your time

Answer 1

i have created the test data fiddle and made an easy and understandable query to fetch the required data, i.e:

SELECT DISTINCT classId,
                std.name,
                Class.email,
                signUpDate
FROM CLASS
INNER JOIN Student std ON std.email = Class.email
WHERE signUpDate IN
    (SELECT max(signUpDate)
     FROM CLASS
     GROUP BY classId)

Sql Fiddle here