{"id":492,"date":"2013-11-19T10:26:00","date_gmt":"2013-11-19T15:26:00","guid":{"rendered":"https:\/\/mkncreations.com\/site\/?p=492"},"modified":"2015-09-15T20:14:20","modified_gmt":"2015-09-16T01:14:20","slug":"tsql-get-latest-record-related-to-another-record","status":"publish","type":"post","link":"https:\/\/mkncreations.com\/site\/2013\/11\/tsql-get-latest-record-related-to-another-record\/","title":{"rendered":"TSQL &#8211; Get Latest Record Related to Another Record"},"content":{"rendered":"<p>If you would like to get only the latest record that is related to another record (Entity) here is one method of doing that by using a derived table:<\/p>\n<p>In the example below we have a Company table that is related to Activity table. We are going to pull the latest Activity related to each Company.<\/p>\n<blockquote><p>\nSELECT Company.Name, la.Subject, la.Date<br \/>\nFROM Company<br \/>\nLEFT JOIN (<br \/>\nSELECT Company.ID AS [CompanyID], Activity.Date, Activity.Subject AS [Subject]<br \/>\n, ROW_NUMBER() OVER ( PARTITION BY Company.ID ORDER BY Activity.Date DESC) as rn<\/p>\n<p>FROM Company<br \/>\nLEFT JOIN Company_Activity ON Company_Activity.Company_ID = Company.ID<br \/>\nLEFT JOIN Activity ON Activity.ID = Company_Activity.Activity_ID<br \/>\n) as la ON la.CompanyID = Company.ID AND la.rn = 1\n<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>If you would like to get only the latest record that is related to another record (Entity) here is one method of doing that by&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,12],"tags":[117,120,79,21,118,119,50,78,49],"class_list":["post-492","post","type-post","status-publish","format-standard","hentry","category-how_to","category-sql","tag-latest","tag-latest-record","tag-microsoft","tag-ms","tag-record","tag-related","tag-sql-2","tag-sql2008","tag-tsql"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/posts\/492"}],"collection":[{"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/comments?post=492"}],"version-history":[{"count":7,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/posts\/492\/revisions"}],"predecessor-version":[{"id":276459,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/posts\/492\/revisions\/276459"}],"wp:attachment":[{"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/media?parent=492"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/categories?post=492"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/tags?post=492"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}