Sql Server Query Selecting Top and grouping by

SpousesTable SpouseID

SpousePreviousAddressesTable PreviousAddressID, SpouseID, FromDate, AddressTypeID

What I have now is updating the most recent for the whole table and assigning the most recent regardless of SpouseID the AddressTypeID = 1

I want to assign the most recent SpousePreviousAddress.AddressTypeID = 1 for each unique SpouseID in the SpousePreviousAddresses table.

UPDATE spa 
SET spa.AddressTypeID = 1
FROM SpousePreviousAddresses AS spa INNER JOIN Spouses ON spa.SpouseID = Spouses.SpouseID,
(SELECT TOP 1 SpousePreviousAddresses.* FROM SpousePreviousAddresses 
    INNER JOIN Spouses AS s ON SpousePreviousAddresses.SpouseID = s.SpouseID 
    WHERE SpousePreviousAddresses.CountryID = 181 ORDER BY SpousePreviousAddresses.FromDate DESC) as us
WHERE spa.PreviousAddressID = us.PreviousAddressID

I think I need a group by but my sql isn't all that hot. Thanks.

Update that is Working

I was wrong about having found a solution to this earlier. Below is the solution I am going with

WITH result AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY SpouseID ORDER BY FromDate DESC) AS rowNumber, *
    FROM SpousePreviousAddresses
    WHERE CountryID = 181
)
UPDATE result
SET AddressTypeID = 1
FROM result WHERE rowNumber = 1

Solution

Presuming you are using SQLServer 2005 (based on the error message you got from the previous attempt) probably the most straightforward way to do this would be to use the ROW_NUMBER() Function couple with a Common Table Expression, I think this might do what you are looking for:

WITH result AS
(
SELECT 
    ROW_NUMBER() OVER (PARTITION BY SpouseID ORDER BY FromDate DESC) as rowNumber,
    * 
FROM 
    SpousePreviousAddresses
)
    UPDATE SpousePreviousAddresses
    SET
        AddressTypeID = 2
    FROM 
        SpousePreviousAddresses spa
            INNER JOIN result r ON spa.SpouseId = r.SpouseId
    WHERE r.rowNumber = 1
            AND spa.PreviousAddressID = r.PreviousAddressID
            AND spa.CountryID = 181

In SQLServer2005 the ROW_NUMBER() function is one of the most powerful around. It is very usefull in lots of situations. The time spent learning about it will be re-paid many times over.

The CTE is used to simplyfy the code abit, as it removes the need for a temporary table of some kind to store the itermediate result.

The resulting query should be fast and efficient. I know the select in the CTE uses *, which is a bit of overkill as we dont need all the columns, but it may help to show what is happening if anyone want to see what is happening inside the query.