Sql Server Query Select Top and Group By
SpousesTable SpouseID
SpousePreviousAddressesTable PreviousAddressID, SpouseID, FromDate, AddressTypeID
Now I update the most recent for the whole table and assign the most recent regardless of SpouseID AddressTypeID = 1
I want to assign the most recent SpousePreviousAddress.AddressTypeID = 1 to 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, but my sql is not that hot. Thank.
An update that works
I was wrong about finding the solution before. 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
source to share
Assuming you are using SQLServer 2005 (based on the error message you got from a previous attempt), probably the easiest way would be to use a couple of ROW_NUMBER () functions with a generic table expression, I think this can do what you 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. This is very useful in many situations. Time spent learning this will be overpaying many times over.
CTE is used to easily remove the abit code as it eliminates the need for a temporary table of some kind to store the result.
The request received should be fast and efficient. I know the selection in the CTE uses *, which is a bit picky as we don't need all the columns, but it might help show what's going on if someone wants to see what's going on inside the query.
source to share
Here's one way to do it:
UPDATE spa1
SET spa1.AddressTypeID = 1
FROM SpousePreviousAddresses AS spa1
LEFT OUTER JOIN SpousePreviousAddresses AS spa2
ON (spa1.SpouseID = spa2.SpouseID AND spa1.FromDate < spa2.FromDate)
WHERE spa1.CountryID = 181 AND spa2.SpouseID IS NULL;
In other words, update a row spa1
for which no other row exists spa2
with the same spouse and a later date.
For each value, SpouseID
there is exactly one row that has the highest date compared to all other rows (if any) with the same SpouseID
.
Not necessary to use GROUP BY
because there is an implicit grouping done by the join.
update: I think you misunderstood the purpose OUTER JOIN
. If there is no row spa2
that matches all of the join conditions, then all columns are spa2.*
returned as NULL. This is how outer joins work. This way you can look for cases where it spa1
doesn't have a matching row spa2
by checking that spa2.SpouseID IS NULL
.
source to share