A simple query that returns the most current address

Hi,

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

Quote:

Originally Posted by

>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.

cheersRex wrote:

Quote:

Originally Posted by

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
>

Quote:

Originally Posted by

>>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
)