{"id":18635,"date":"2018-03-03T14:17:00","date_gmt":"2018-03-03T14:17:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/premier_developer\/?p=18635"},"modified":"2019-02-14T20:18:28","modified_gmt":"2019-02-15T03:18:28","slug":"using-join-with-max-to-write-efficient-queries","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/premier-developer\/using-join-with-max-to-write-efficient-queries\/","title":{"rendered":"Using JOIN with MAX to write efficient queries"},"content":{"rendered":"

In this post, Senior Application Development Manager, Alexei Govorine<\/a>,  demonstrates how to use SQL JOIN and MAX in a query.<\/p>\n

\n

How to join two SQL tables and display the latest results.<\/p>\n

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.<\/p>\n

The answer is to use JOIN<\/b> and MAX<\/b> statements in a query. <\/p>\n

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. <\/p>\n

Not all students may have grade records and mostly of them may have multiple entries.<\/p>\n

Table: Student Table: StudentGrades<\/p>\n\n\n\n\n\n\n\n\n
\n

id<\/b><\/p>\n<\/td>\n

\n

first<\/b><\/p>\n<\/td>\n

\n

last<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

1<\/p>\n<\/td>\n

\n

John<\/p>\n<\/td>\n

\n

Smith<\/p>\n<\/td>\n<\/tr>\n

\n

2<\/p>\n<\/td>\n

\n

Mary<\/p>\n<\/td>\n

\n

Johnson<\/p>\n<\/td>\n<\/tr>\n

\n

3<\/p>\n<\/td>\n

\n

Michael<\/p>\n<\/td>\n

\n

White<\/p>\n<\/td>\n<\/tr>\n

\n

4<\/p>\n<\/td>\n

\n

Maria<\/p>\n<\/td>\n

\n

Garcia<\/p>\n<\/td>\n<\/tr>\n

\n

5<\/p>\n<\/td>\n

\n

David<\/p>\n<\/td>\n

\n

Jones<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n

Id<\/b><\/p>\n<\/td>\n

\n

student_id<\/b><\/p>\n<\/td>\n

\n

school_year<\/b><\/p>\n<\/td>\n

\n

gpa<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

1<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n

\n

2016 Q1<\/p>\n<\/td>\n

\n

3.6<\/p>\n<\/td>\n<\/tr>\n

\n

2<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n

\n

2016 Q2<\/p>\n<\/td>\n

\n

3.7<\/p>\n<\/td>\n<\/tr>\n

\n

3<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n

\n

2016 Q3<\/p>\n<\/td>\n

\n

3.7<\/p>\n<\/td>\n<\/tr>\n

\n

4<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n

\n

2017 Q1<\/p>\n<\/td>\n

\n

3.6<\/p>\n<\/td>\n<\/tr>\n

\n

5<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n

\n

2017 Q2<\/p>\n<\/td>\n

\n

4.0<\/p>\n<\/td>\n<\/tr>\n

\n

6<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n

\n

2017 Q3<\/p>\n<\/td>\n

\n

3.9<\/p>\n<\/td>\n<\/tr>\n

\n

7<\/p>\n<\/td>\n

\n

2<\/p>\n<\/td>\n

\n

2016 Q1<\/p>\n<\/td>\n

\n

3.1<\/p>\n<\/td>\n<\/tr>\n

\n

8<\/p>\n<\/td>\n

\n

2<\/p>\n<\/td>\n

\n

2016 Q2<\/p>\n<\/td>\n

\n

3.3<\/p>\n<\/td>\n<\/tr>\n

\n

9<\/p>\n<\/td>\n

\n

2<\/p>\n<\/td>\n

\n

2016 Q3<\/p>\n<\/td>\n

\n

3.5<\/p>\n<\/td>\n<\/tr>\n

\n

10<\/p>\n<\/td>\n

\n

2<\/p>\n<\/td>\n

\n

2017 Q1<\/p>\n<\/td>\n

\n

3.5<\/p>\n<\/td>\n<\/tr>\n

\n

11<\/p>\n<\/td>\n

\n

2<\/p>\n<\/td>\n

\n

2017 Q2<\/p>\n<\/td>\n

\n

3.9<\/p>\n<\/td>\n<\/tr>\n

\n

12<\/p>\n<\/td>\n

\n

2<\/p>\n<\/td>\n

\n

2017 Q3<\/p>\n<\/td>\n

\n

3.1<\/p>\n<\/td>\n<\/tr>\n

\n

13<\/p>\n<\/td>\n

\n

4<\/p>\n<\/td>\n

\n

2017 Q1<\/p>\n<\/td>\n

\n

3.3<\/p>\n<\/td>\n<\/tr>\n

\n

14<\/p>\n<\/td>\n

\n

5<\/p>\n<\/td>\n

\n

2016 Q1<\/p>\n<\/td>\n

\n

3.4<\/p>\n<\/td>\n<\/tr>\n

\n

15<\/p>\n<\/td>\n

\n

5<\/p>\n<\/td>\n

\n

2016 Q1<\/p>\n<\/td>\n

\n

3.7<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n


<\/p>\n

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<\/p><\/pre>\n
Sixteen student grade records will be returned by using only a LEFT OUTER JOIN in the query.<\/p>\n
Altering the query to include a subquery with MAX on record id, results with student latest GPA data.<\/p>\n
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<\/b>.<\/b>record_id <\/b>=<\/b> <\/b>(<\/b>SELECT <\/b>MAX<\/b>(<\/b>record_id<\/b>)<\/b> FROM StudentGrades 
WHERE student_id=sd.student_id)<\/p><\/pre>\n

<\/p>\n\n\n\n\n\n\n\n\n
\n
id<\/b><\/p>\n<\/td>\n
\n
first<\/b><\/p>\n<\/td>\n
\n
last<\/b><\/p>\n<\/td>\n
\n
school_year<\/b><\/p>\n<\/td>\n
\n
gpa<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
1<\/p>\n<\/td>\n
\n
John<\/p>\n<\/td>\n
\n
Smith<\/p>\n<\/td>\n
\n
2017Q3<\/p>\n<\/td>\n
\n
3.9<\/p>\n<\/td>\n<\/tr>\n
\n
2<\/p>\n<\/td>\n
\n
Mary<\/p>\n<\/td>\n
\n
Johnson<\/p>\n<\/td>\n
\n
2017Q3<\/p>\n<\/td>\n
\n
3.1<\/p>\n<\/td>\n<\/tr>\n
\n
3<\/p>\n<\/td>\n
\n
Michael<\/p>\n<\/td>\n
\n
White<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n<\/tr>\n
\n
4<\/p>\n<\/td>\n
\n
Maria<\/p>\n<\/td>\n
\n
Garcia<\/p>\n<\/td>\n
\n
2017Q1<\/p>\n<\/td>\n
\n
3.3<\/p>\n<\/td>\n<\/tr>\n
\n
5<\/p>\n<\/td>\n
\n
David<\/p>\n<\/td>\n
\n
Jones<\/p>\n<\/td>\n
\n
2016Q1<\/p>\n<\/td>\n
\n
3.7<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n
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.<\/p>\n
For additional information on MAX and other aggregate SQL functions see https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/functions\/max-transact-sql<\/a><\/p>\n
\n
Premier Support for Developers<\/strong><\/a> 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<\/b><\/a> to learn more about what we can do for you.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":582,"featured_media":37840,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[8],"tags":[10,3],"class_list":["post-18635","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data","tag-sql","tag-team"],"acf":[],"blog_post_summary":"
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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts\/18635","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/users\/582"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/comments?post=18635"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts\/18635\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/media\/37840"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/media?parent=18635"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/categories?post=18635"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/tags?post=18635"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}