In this post, Senior Application Development Manager, Alexei Govorine, demonstrates how to use SQL JOIN and MAX in a query.
How to join two SQL tables and display the latest results.
Recently a customer has asked me to help them with a query design. The question was how to join two tables and display the latest results from one of them in a single query.
The answer is to use JOIN and MAX statements in a query.
To demonstrate the correct syntax, let consider: We need to display the latest student GPA records from the data that is contained in two tables: Student and StudentGrades.
Not all students may have grade records and mostly of them may have multiple entries.
Table: Student Table: StudentGrades
id |
first |
last |
1 |
John |
Smith |
2 |
Mary |
Johnson |
3 |
Michael |
White |
4 |
Maria |
Garcia |
5 |
David |
Jones |
Id |
student_id |
school_year |
gpa |
1 |
1 |
2016 Q1 |
3.6 |
2 |
1 |
2016 Q2 |
3.7 |
3 |
1 |
2016 Q3 |
3.7 |
4 |
1 |
2017 Q1 |
3.6 |
5 |
1 |
2017 Q2 |
4.0 |
6 |
1 |
2017 Q3 |
3.9 |
7 |
2 |
2016 Q1 |
3.1 |
8 |
2 |
2016 Q2 |
3.3 |
9 |
2 |
2016 Q3 |
3.5 |
10 |
2 |
2017 Q1 |
3.5 |
11 |
2 |
2017 Q2 |
3.9 |
12 |
2 |
2017 Q3 |
3.1 |
13 |
4 |
2017 Q1 |
3.3 |
14 |
5 |
2016 Q1 |
3.4 |
15 |
5 |
2016 Q1 |
3.7 |
SELECT s.id, s.first, s.last, sd.school_year, sd.gpa FROM Student sLEFT OUTER JOIN StudentGrades sd ON s.id=sd.student_id
Sixteen student grade records will be returned by using only a LEFT OUTER JOIN in the query.
Altering the query to include a subquery with MAX on record id, results with student latest GPA data.
SELECT s.id, s.first, s.last, sd.school_year, sd.gpa FROM Student sLEFT OUTER JOIN StudentGrades sd ON s.id=sd.student_id
AND sd.record_id = (SELECT MAX(record_id) FROM StudentGrades
WHERE student_id=sd.student_id)
id |
first |
last |
school_year |
gpa |
1 |
John |
Smith |
2017Q3 |
3.9 |
2 |
Mary |
Johnson |
2017Q3 |
3.1 |
3 |
Michael |
White |
NULL |
NULL |
4 |
Maria |
Garcia |
2017Q1 |
3.3 |
5 |
David |
Jones |
2016Q1 |
3.7 |
Alternative we could use MAX function on some other column like the date data was entered and in conjunction with ORDER BY and WHERE statements to address more complicated situations.
For additional information on MAX and other aggregate SQL functions see https://docs.microsoft.com/en-us/sql/t-sql/functions/max-transact-sql
Premier Support for Developers provides strategic technology guidance, critical support coverage, and a range of essential services to help teams optimize development lifecycles and improve software quality. Contact your Application Development Manager (ADM) or email us to learn more about what we can do for you.
Above the Query which Field Name is Record_Id in table