Using JOIN with MAX to write efficient queries


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 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)



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.

Comments (2)

  1. MAX is a deterministic function

  2. Kevin Buchan says:

    This was very interesting, but I feel like this missed an opportunity to talk about ROW_NUMBER.

    Something like adding this for a column def:
    ROW_NUMBER() Over (Partition By StudentID Order By School_Year) as Seq

    Then this for your WHERE clause:
    WHERE Seq = 1

    This also doesn’t require the PK for StudentGrades to be created in sequence since your sorting by something which has meaning.

Skip to main content