Posted by Plamen Ratchev on 07/24/07 03:07

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

[Back to original message]