Posted by Rex on 07/24/07 04:46

On Jul 24, 12:07 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> 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 :)

• Next in forum: Re: Ongoing purging of active records causes deadlocks
• Prev in forum: Re: A simple query that returns the most current address
• Thread view: Re: A simple query that returns the most current address

[Reply to this message]