SQL-Use most recent address

[Mbroad]

Hello--I am writing SQL which utilizes several different tables to do a basic name and address extract. The name is one TABLEA and the Address is on TABLEB, however there are often multiple addresses for each name. there is a field on TABLEB which has the update date and time, so you would kmow the most recent address for that name.

I would like to know how I can select ONLY the most recent address for the name. The linking field in this case is called CASE #.
Your help is greatly appreciated.
MB

Government Coder Mark

 

[gmmastros]

I my opinion, you should use a subquery.

For example...

Select CaseNumber,
       Max(DateTimeColumn) As MaxDateTime
From   TableB
Group By CaseNumber

This will return the case number and the max date time.

Then, link back to the table to get the other fields, like so...

Select TableA.FieldList ...,
       TableB.FieldList ...
From   TableB
       Inner Join 
         (
         Select CaseNumber,
                Max(DateTimeColumn) As MaxDateTime
         From   TableB
         Group By CaseNumber
         ) As MaxDates
         On  TableB.CaseNumber = MaxDates.CaseNumber
         And TableB.DateTimeColumn = MaxDates.MaxDateTime
       Inner Join TableA
         On TableB.CaseNumber = TableA.CaseNumber

Hope this helps...

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

SELECT Name, (SELECT TOP 1 TableB.Address FROM TableB WHERE TableB.FK = TableA.PK ORDER BY FieldThatIsUsed DESC) AS Address
FROM TableA

(not tested)

Borislav Borissov