Another Max Date Question

[amy3000]

I've looked through many of the Max date threads but don't really know SQL too well. I did something that I think works but I'm not sure this is the right way to do this. I have an address table with the fields ID#, Address, and Effective Date. The addresses are updated every month which is the Effective date. I want to see the only the most current address without any duplicates.

A straight query looks like this:

ID# Address Effective Date

1 address 1 5/1/04
1 address 2 6/1/04
1 address 2 7/1/04

A totals query with date set to Max looks like this. It's grouped on SSN and Address so I'm getting multiple addresses:

ID# Address Effective Date
Group Group Max
1 address 1 5/1/04
1 address 2 7/1/04

A query with Address set to Last and Date to Max looks right, the most current entry, but I'm not sure it is:
ID# Address Effective Date
Group Last Max
1 Address 2 7/1/04

This is the SQL:
SELECT dbo_tbl_Address.nbrID, Last(dbo_tbl_Address.txtMailAddr1) AS LastOftxtMailAddr1, Max(dbo_tbl_Address.dtmEffectiveDate) AS MaxOfdtmEffectiveDate
FROM dbo_tbl_Address
GROUP BY dbo_tbl_Address.nbrID;

Thanks in advance.

 

[Golom]

You probably need

SELECT 
   A.nbrID, 
   A.txtMailAddr1, 
   A.dtmEffectiveDate

FROM dbo_tbl_Address As A

Where A.dtmEffectiveDate = 
      (Select MAX(B.dtmEffectiveDate) 
       From  dbo_tbl_Address As B
       Where B.nbrID = A.nbrID)

The code that you have selects the LAST address in the current sort order which is undefined because there is no ORDER BY clause. That's not necessarily the one that is associated with the MAX(EffectiveDate) value.

 

[PHV]

Another way is to create a ved query named, say, qryLastDate:
SELECT nbrID, Max(dtmEffectiveDate) AS LastDate
FROM dbo_tbl_Address
GROUP BY nbrID;
Then your SQL is now:
SELECT A.nbrID, A.txtMailAddr1, A.dtmEffectiveDate
FROM dbo_tbl_Address A INNER JOIN qryLastDate L ON A.nbrID = L.nbrID AND A.dtmEffectiveDate = L.LastDate;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[amy3000]

Thanks to both of you. Both of these work.