Home SQL Select the most recent record for each ID
Reply: 0

SQL Select the most recent record for each ID

user1097
1#
user1097 Published in May 25, 2018, 6:56 pm

I have two tables, the first one is like this:

     INTERNSHIP
ID|  Term
---------
1 | 2015
1 | 2014
2 | 2016
2 | 2017

The second one is like this:

       Term
Term| Term Description | End Date
---------------------------------
2014 |  Summer 2014   | 8/12014
2014 |  Summer 2014   | 8/12014
2015 |  Fall 2015     | 12/1/2015
2017 |  Spring 2017   | 4/1/2017
2017 |  Spring 2017   | 6/1/2017

I need to find the "Term Description" for the most recent term for all the IDs. The desired result is like this:

ID| Term | Term Description 
------------------
1 |  2015   |  Fall 2015   
2 |  2017   |  Spring 2017

I did some research and came up with a solution like this:

SELECT INTERNSHIP.ID, INTERNSHIP.Term
FROM INTERNSHIP
WHERE (
  SELECT MAX(INTERNSHIP.Term)
  )
GROUP BY INTERNSHIP.ID

By that I got the distinct IDs with the most recent term, but I could not JOIN that with the Term Description. Can someone help me to get the desired result?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.309637 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO