Posted by Ed Murphy on 07/24/07 04:07

Rex wrote:

> I have an Address table which contains more than one addresses for a
> particular member. I want to write a query that would only display
> most current address. All addresses have a unique ID (addID).
> Example:
>
> memberID addID address1
> -------- ------ --------------------------------------------------
> 295 69 13 Auster St
> 295 70 465 Lorre Ct
> 295 71 P.O. Box 321
> 722 171 10 Hannaford Rd
> 722 172 Dubai, United Arab Emirates
>
>>From the table data above. The query should only return
>
> memberID addID address1
> -------- ------ --------------------------------------------------
> 295 71 P.O. Box 321
> 722 172 Dubai, United Arab Emirates
>
> I tried using Max and Group by function but it shows me all the rows.
> If you can provide me with a sample code that would greatly
> appreciated.

Assuming that each member's most current address has the largest
addID value, and that addID values are not re-used from one member
to the next:

select memberID, addID, address1
from the_table
where addID in (
select max(addID)
from the_table
group by memberID
)

[Back to original message]