Solved

SQL Join on Most Recent Record

Posted on 2007-11-20
3
884 Views
Last Modified: 2010-05-18
I have two tables in SQL, table one has information, and table two has more dynamic information that is related to table one.  

Table1
ID
LRSN
INFO
MOREINFO

Table 2
ID
LRSN
DESCRIPTION
LAST_UPDATE

I would like to join the two tables on LRSN, and pull the Description of the most recent Last_Update record from table two.  Can someone help me out with this relationship?  Thanks in advance for the help!
0
Comment
Question by:jayh99
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 20319619
select ...
from table1 t1
join table2 t2
  on t1.lrsn = t2.lrsn
 and t2.last_update = ( select max(i.last_update) from table2 i where i.lrsn = t2.lrsn )
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20319625
SELECT *
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.LRSN = t2.Lrsn
WHERE T2.Last_Update = (SELECT MAx(last_update) from tabel2 where lrsn = t2.lrsn)
0
 
LVL 1

Author Closing Comment

by:jayh99
ID: 31410115
Excellent!  Thanks for the quick response!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now