MySQL Select entire row from matching Group By value

Question

Lets say I have 2 tables like so:

MyTable1:
Name    ID      Timestamp            TestNum    Grade
Alex    1101    2020-10-01 12:00:00  1          85
Alex    1101    2020-10-02 13:00:00  2          90
Alex    1101    2020-10-03  8:00:00  3          95
Alex    1101    2020-10-04 10:00:00  4          90

MyTable2:
ID      Avg  StDev
1101    90   4.08

I am trying to get the row of the first (Timestamp) instance where the grade was X standard deviations away.

ExpectedResults:
Name    ID      Timestamp            TestNum    StDevsAway
Alex    1101    2020-10-01 12:00:00  1          -1.23
Alex    1101    2020-10-02 13:00:00  2          0
Alex    1101    2020-10-03  8:00:00  3          1.23

The 4th row should not be returned as its Standard Deviations Away was already found at a previous Timestamp.

I'm still fairly new to MySQL, but this is where I'm at so far:

select a.Name
     , a.ID
     , a.Timestamp
     , a.TestNum
     , round( ( a.Grade - b.Avg ) / b.StDev, 2 ) as StDevsAway 
  from MyTable1 as a 
  join MyTable2 as b 
    on a.ID = b.ID 
 group 
    by round( ( a.Grade - b.Avg ) / b.StDev, 2 );

Answer 1

I think the question is just about finding "first" row for each id/grade tupe. So (asssuming MySQL 8.0):

select t1.*
from (
    select t1.*, row_number() over(partition by id, grade order by timestamp) rn
    from mytable1 t1
) t1
where rn = 1

Then, you can bring the second table with a join if you like:

select t1.*, round(t1.grade - t2.avg) / t2.stdev, 2) stdevsaway
from (
    select t1.*, row_number() over(partition by id, grade order by timestamp) rn
    from mytable1 t1
) t1
inner join mytable2 t2 on t2.id = t1.id
where rn = 1

In earlier versions, you can filter with a subquery:

select t1.*, round(t1.grade - t2.avg) / t2.stdev, 2) stdevsaway
from mytable1 t1
inner join mytable2 t2 on t2.id = t1.id
where t1.timestamp = (
    select min(t11.timestamp) from mytable1 t11 where t11.id = t1.id and t11.grade = t1.grade
)