Using JOIN with MAX to write efficient queries

Developer Support

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

SELECT s.id, s.first, s.last, sd.school_year, sd.gpa FROM Student s 
LEFT 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 s
LEFT 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)

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.

Developer Support App Dev Customer Success Account Manager, Microsoft Developer Support

Follow