March 3rd, 2018

Using JOIN with MAX to write efficient queries

Developer Support
Cloud Solution Architects

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.

Category
Data
Topics
SQLTeam

Author

Developer Support
Cloud Solution Architects

Microsoft Developer Support helps software developers rapidly build and deploy quality applications for Microsoft platforms.

1 comment

Discussion is closed. Login to edit/delete existing comments.

  • Godof War

    Above the Query which Field Name is Record_Id in table