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

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

cheers

Re: A simple query that returns the most current address

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

Re: A simple query that returns the most current address

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
)

Re: A simple query that returns the most current address

On Jul 24, 12:07 pm, "Plamen Ratchev" wrote:
> 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 Ratchevhttp://www.SQLStudio.com

Thanks a lot :)