Question 21810 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL Get Value If Condition is met

I am struggling to figure this out, I have a simple MySQL table (test_scores) that is three columns, id(students), date and test grade.

id date grade
1 10/20/22 A
2 10/20/22 B
1 11/1/22 F
2 11/1/22 C
3 11/1/22 A
1 12/1/22 C

I want to get the grade a student gets on the next test after they get an F.
In this example I would like to get the following output.

id grade next_grade
1 F C

I have tried:

SELECT id, grade, grade as next_grade FROM test_scores WHERE grade = F;

I’m just learning advanced joins and nested queries, and I just can’t figure out how to get the next grade. Any help would be appreciated.

Tags:

2

Answers


  1. 0

    There is plenty of room for optimization.

    First you have to guess, that each student can’t have many graduation, but number of student is big. Also not many student have F.

    So. First you have do is get all students ever get F grade

    select id, `date` from test_scores where grade='F'

    After that you need try to find another grade for that students.

    select a.id,a.grade,b.grade from 
  (select id,`date`,grade from test_scores where grade = 'F')  a 
   left join
      test_scores b on 
        a.id = b.id and a.date < b.date
where b.grade is not null

    This one give all scores which was after F. i.e will give two rows if you have F,A,B. But this should not happens, no?

    You can replace left join with lookup if you think you can have more than one row, but it will be slightly less speed.

    select * from 
   (select id, grade,
                     (select grade from test_scores AS b 
                                  where b.id=a.id and a.date < b.date 
                                   order by b.date limit 1
                     ) AS next_grade
        from test_scores AS b 
           where grade = 'F'
    ) AS result
    where result.next_grade is not null

    Recomended indexes – test_score(id) – I hope you have it. I mean you HAVE to make index on primary column in most cases.

    Maybe can be useful test_score(id,date) – but this has to be checked on real data. Likely will be useful from 10m+ tables, but very low chance. just because every single student can’t have really many graduations results in his life.

    ps. this will work only on date or timestamp column. If your column is text replace join to

    a.id = b.id and 
   DATE_FORMAT(a.date, '%M/%d/%Y') < DATE_FORMAT(b.date, '%M/%d/%Y')

    p.p.s. if your tables are really big you may consider not do subquery, but move where from first subquery outside and just do left join. Which convert this SQL from index lookup type to index match type. It may or may not speed up it.

    Login or Signup to reply.
  2. 0

    Using lead will be much easier, but it requires MySQL 8+.

    select id,
       grade,
       next_grade
from (  select id,
               grade,
               lead (grade) over (partition by id order by str_to_date(date,'%m/%d/%y') asc  ) as next_grade
        from test_scores 
      ) as tbl
where  grade='F' ;

    https://dbfiddle.uk/k2-FDxQO

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search