Using JOIN with MAX to write efficient queries

Premier Developer

Premier

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

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.

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.

Premier Developer
Premier Developer

Premier Support for Developers

Follow Premier   

1 comment

Leave a comment