0

I have two separate tables with the following data. I know finding the elements with the max value in a field is a common question, but I haven't found a way to do this in my situation where the data is spread over two tables.

Enrollment Table:

StudentID CourseID Section_Key Date_Added

Section Table:

SectionID Period

What I'm trying to do is show an entire student's schedule, but remove older courses that have been dropped. The Enrollment table includes courses that have been dropped so I want to only show the most recently added course for each Period.

For example, say there is a student with the following classes in their schedule:

CourseID Section_Key Period Date_Added
 2503F     123456      1     2018-05-13
 9990      245678      1     2018-08-01

I only want it to show the second course above in the results. I would also like the results sorted by Period.

Derek Miller
  • 111
  • 3
  • Can you add a data example of your tables to the question? – Maksym Fedorov Sep 14 '18 at 16:17
  • It sounds like you want to group the results by studentid and period and take the record with the max date_added for each group. A bit like https://stackoverflow.com/questions/2363237/sql-request-with-group-by-and-max-and-join or https://stackoverflow.com/questions/13697607/mysql-left-join-using-max-group-by-on-joined-table – Ryan Dawson Sep 14 '18 at 16:31
  • @RyanDawson I think the MAX function might work, but how do I use it when the data is in two different tables? I need to find the max date for two classes of a single student that are in the same class period and Period and Date_Added are in different tables. – Derek Miller Sep 14 '18 at 16:58

0 Answers0