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

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.

cheers

Here are a few ways:

-- SQL Server 2000
SELECT memberID, addID, address1
FROM Addresses AS A
WHERE addID IN (
SELECT TOP 1 addID
FROM Addresses AS A1
WHERE A1.memberID = A.memberID
ORDER BY A1.addID DESC)

-- or
SELECT memberID, addID, address1
FROM Addresses AS A
WHERE addID = (
SELECT MAX(addID)
FROM Addresses AS A1
WHERE A1.memberID = A.memberID)

-- SQL Server 2005
;WITH cte
(memberID, addID, address1, rn)
AS
(
SELECT memberID, addID, address1,
ROW_NUMBER() OVER(
PARTITION BY memberID
ORDER BY addID DESC)
FROM Addresses
)
SELECT memberID, addID, address1
FROM cte
WHERE rn < 2;


HTH,

Plamen Ratchev
http://www.SQLStudio.com

Rex wrote:

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
)

On Jul 24, 12:07 pm, "Plamen Ratchev" <Pla... (AT) SQLStudio (DOT) com> wrote:
Thanks a lot